II09603 - ODBC TROUBLESHOOTING - EXPRESS, WIN 95/NT, WIN 3.1, OPT FOR OS/2

 APAR (Authorized Program Analysis Report)

Abstract

ODBC TROUBLESHOOTING - EXPRESS, WIN 95/NT, WIN 3.1, OPT FOR OS/2

Error Description

** Last updated January 26, 2000 **                            
*** This is a continuation of II09599 ***                      
                                                               
This information APAR has been updated to include Client Access
Express for Windows.  If a section does not apply to Express    
either the section title will indicate which client the        
section is for, or a note at the beginning of the section will  
indicate that the text does not apply to Express.              
                                                               
 CONTENTS                                                      
 ========                                                      
 1.0 ........................ Unable to connect to the AS/400  
 2.0 ............................. How to locate the ODBC job  
 3.0 .......................................... Common Errors  
     3.1 .................................. Connection Errors  
     3.2 ........................ Application Specific Errors  
     3.3 ............................ Stored Procedure Errors  
     3.4 ........................................ Misc Errors  
 4.0 ...................... Reporting problems to IBM Support  
 5.0 ....................... Diagnostic and Performance Tools  
 6.0 ............................................... Security  
 7.0 ....................................... Other References  
                   3.0 COMMON ERRORS                            
                   ==================                          
                                                               
 3.1  Connection Errors                                        
 ======================                                        
 Undefined Dynalink                                            
 ------------------                                            
 ** This section does not apply to Client Access Express.      
                                                               
 This is a Windows error generated when an application fails    
 to successfully load a dependent dll.  Either the dll wasn't  
 found in the search path or the dll was found but the          
 function was not. This is a common problem when the user      
 has loaded multiple versions of AS/400 connectivity software.  
 Some software vendors load their DLL's in the WINDOWS\SYSTEM  
 directory.  This is the first directory most applications      
 look for to find their DLL's.  If you are switching from a    
 OEM product to Client Access, then you will have to remove    
 any DLL's that begin with EHN from the WINDOWS\SYSTEM          
 directory and any other directory in the path that precedes    
 the CAWIN or PCS path. If the PC is connected to a network    
 file server, keep in mind that network drives are also        
 included in the default search path. Windows searches in the  
 following order:  Current directory, windows,                  
 windows/system, PATH, network drives.                          
                                                               
 Communication Link Failure APPC RC0x36                        
 --------------------------------------                        
 ** This section does not apply to Client Access Express.      
                                                               
 This error message occurs when the Database Server prestart    
 job QZDAINIT isn't active or on the system.  Try starting      
 it manually using the steps provided above.  If that fails,    
 use the "Unable to Connect" checklist provided above.          
                                                               
 Communication Link Failure APPC RC0x32 and  RC0x6              
 -------------------------------------------------              
 ** This section does not apply to Client Access Express.      
                                                               
 This error may occur when using an SNA connection              
 to the AS/400 and passing through a network of other          
 AS/400's and VTAM's.  To resolve this                          
 error, create a QSERVER mode description on all systems        
 that the conversation passes Through.  Use the same            
 parameters that are specified for the existing QSERVER        
 mode description. On an AS/400, issue the following command:  
                                                               
     CRTMODD MODD(QSERVER) MAXSSN(64) MAXCNV(64) LCLCTLSSN(0)  
     INPACING(8) OUTPACING(8)                                  
                                                               
 Invalid Security APPC RC0x32 or RC0x37                        
 -------------------------------------                          
 ** This section does not apply to Client Access Express.      
                                                               
 This error is usually seen when an invalid userid or          
 password is supplied on an ODBC connect statement (rather      
 then using the default userid of the router).  SA51888 fixed  
 a problem in the OS/2 vdm router that incorrectly generated    
 this error when running ODBC                                  
 in a winos2 environment across the virtual extended DOS        
 router. Obtain the the latest PTF from service for a          
 solution.                                                      
                                                               
 Communication Link Failure APPC RC0x46                        
 --------------------------------------                        
 ** This section does not apply to Client Access Express.      
                                                               
 This error is caused by the Database Server on the AS/400      
 terminating abnormally.  Call your IBM Support Line            
 representative to assist in problem determination.            
 See GATHERING INFORMATION FOR IBM SUPPORT below for details.  
                                                               
 Communication Link Failure APPC RC0x51                        
 --------------------------------------                        
 ** This section does not apply to Client Access Express.      
                                                               
 This results from using an incorrect system name in the        
 ODBC data source.  Users of the Windows ODBC driver under      
 OS/2 may see this error if they mistakenly used the system    
 name instead of the CM/2 (or CM/400) Partner LU ALIAS name.    
 Run the Communications Manager configuration program and      
 verify the Partner LU Alias name.                              
 This information can also be found in the RMTN statement of    
 the PCSOS2\CONFIG.PCS file.                                    
                                                               
 ODBC Driver Not Capable APPC RC0x63                            
 -------------------------------------                          
 ** This section does not apply to Client Access Express.      
                                                               
 This error is usually seen when there's an                    
 older version of an ODBC.DLL driver being used.                
 Check for duplicate ODBC drivers on the pc.                    
 Also check for old versions of MSAJT200.DLL if                
 using Microsoft Access 2.0.                                    
                                                               
 APPC RC0x85 or  RC0x87                                        
 -----------------------                                        
 ** This section does not apply to Client Access Express.      
                                                               
 This RC is only defined for the Netsoft router.  Obtain the    
 latest router fixes from your vendor (Novell SAA or Netsoft).  
 See "Dynalink Error" above for tips on solving duplicate dll  
 problems.                                                      
                                                               
 MSGSQL0113 -  Name &1 not allowed.                            
 ---------------------------------                              
 See MSGSQL0114                                                
                                                               
 MSGSQL0114 - Relational database &1 not the same as current    
 &2 server                                                      
 -----------------------------------------------------------    
 o The system name is most likely not added to the Remote      
   Database Directory.  Run the following command:              
                                                               
          ADDRDBDIRE RDB(SYSNAME) RMTLOCNAME(*LOCAL)            
                                                               
   Where SYSNAME is the name of your system's Default Local    
   Location name (as specified in the DSPNETA command).        
                                                               
 o Another common cause for this error is a period (".") in a  
   table or   library name.  Although valid in AS/400 naming    
   conventions, this is an invalid character for ODBC and SQL  
   syntax.  C  onsider changing file naming conventions to      
   conform to AN  SI 92 SQL syntax.                            
   A short term circumvention may be to build a logical file    
   over the desired physical file, using the SQL naming syntax.
                                                               
 MSGSQL0122 - Column &1 or function specified in SELECT...      
 ---------------------------------------------------------      
 If there is a GROUP BY clause in the SQL Select statement,    
 the same fields that are specified in the GROUP BY must be    
 in the SELECT field list.                                      
                                                               
 MSGSQL0204 - MYSYSCONF not found                              
 --------------------------------                              
 This message is always seen in the joblogs for jobs using      
 the Microsoft Jet Engine (MS ACCESS or MS Visual Basic        
 applications). The MS Jet Engine used by these applications    
 always checks for an optional table on the server called      
 MSYSCONF. The warning is ignored by the applications.  For    
 further information see the MS Jet Database                    
 Engine Connectivity white paper or contact Microsoft.          
                                                               
 MSGSQL0900 - Application process not in a connected state      
 ---------------------------------------------------------      
 See SQL5016 and SQL0114.                                      
                                                               
 MSGSQL5001 - Column qualifier or table &2 undefined.          
 MSGSQL5016 - Object name &1 not valid for naming convention    
 ------------------------------------------------------------  
 Your Data Source Name (DSN) is incorrectly configured to      
 use the wrong naming convention.  Use the ODBC Administrator  
 to change your DSN to use the proper (*SQL or *SYS) naming    
 convention. "*SQL" should always be used unless your          
 application is specifically designed for *SYS.                
                                                               
                                                               
 MSGSQL0104  Token &1 was not valid. Valid tokens: &2.          
 -----------------------------------------------------          
  -  The application generated a SQL statement with incorrect  
     syntax.                                                    
  -  If the token is "*" see SQL0114.                          
  -  ODBC.DLL is down level.  See "Conformance Error -7716".    
  -  The SQL statement exceeded the AS/400's 32K size          
     limitation.                                                
  -  The application is using incorrect syntax for left outer  
     join. Some applications default to a proprietary left      
     outer join syntax of     *= in the WHERE clause            
     (Powerbuilder 3.0 & 4.0, Cry stal Reports).                
     Check with your application vendor.  Most provide an      
     ini setting     or configuration value to use ODBC left    
     outer join syntax.                                        
                                                               
                                                               
 MSGSQL7008  &1 in &2 not valid for operation.  The reason      
 code is 3.                                                    
 ------------------------------------------------------------  
 The AS/400 implements commitment control by journaling.        
 Any ODBC application that takes advantage of commitment        
 control will require that the files used be journaled.        
                                                               
 3.2 APPLICATION SPECIFIC ERRORS                                
 ================================                              
                                                               
 3.2.1 CONFORMANCE ERRORS with Microsoft Access                
 ==============================================                
 -   -7711                                                      
     o Router not started.                                      
     o Incorrect naming convention used (see SQL5016).          
     o A library specified in the ODBC Data Source does not    
       exist on the AS/400.  Use the ODBC administration        
       program to reconfigure the driver.                      
     o Duplicate EHNAPPC dll's or mixed versions of            
       connectivity code. See "Dynalink Error".                
     o If using the Client Access for Windows 3.1 ODBC driver  
       with the Client Access for extended DOS router, verify  
       that Client Access has been configured for Windows      
       coexistence: i.e. the PCSWIN TSR is loaded before        
       loading windows.                                        
                                                               
 -   -7716                                                      
     o If this occurs when using an OEM router or the  Client  
       Access for Extended DOS router, then obtain the latest  
       ODBC host server PTF.                                    
     o If using Client Access for Windows 3.11 or later ODBC    
       driver, then verify that the ODBC driver manager        
       (ODBC.DLL) is at version 2 of the ODBC specification.    
       The size and date of ODBC.DLL and ODBCADM.DLL in your    
       windows system directory should match those in the      
       AS/400 directory \QPWXCWN.                              
                                                               
 -   -7738                                                      
     o If this error occurs when accessing a physical file      
       with logical files built over it, try copying the        
       physical file to a new library and querying that file.  
       If this works, there is a problem with with the logical  
       files built over the physical file.  See apar SA45547    
       for the latest ODBC host server PTF and contact AS/400  
       technical support for any additional PTFs that may be    
       required to run with it.                                
     o If the latest PTFs are applied, and you still get the    
       -7738 error, it is possible that an index over the      
       physical file is corrupted and will need to be rebuilt.  
       If accessing a physical file that has a join logical    
       file over it, the join logical file may not be keyed.    
                                                               
 -   -7761                                                      
     The Client Access for Windows R311 ODBC driver was        
     updated to return full support ("F") for left outer        
     join support. This value was added in the R2 ODBC          
     specification. Older versions of the MS Jet Engine        
     included with MS ACCE SS report this as a 7761            
     conformance error: "valid values are only Y or N".        
     Obtain the latest Jet Engine fix pack from microsoft      
     support.                                                  
                                                               
 -   Missing data or Incorrect Data                            
     o ACCESS may not display all fields if the total number    
       exceeds 200 fields.  Contact Microsoft support for      
       further in formation on record size limitations with    
       MS ACCESS 2.0.                                          
                                                               
 -   o ACCESS may implement left outer joins by splitting      
       the statement into two separate selects, downloading    
       all the data, and then joining the two temporary        
       tables. This can potentially lead to EXTREMELY large    
       temporary files and unpredictable results. Contact      
       Microsoft for information on how ACCESS can take        
       advantage of full left outs which the AS/400 supports.  
                                                               
 3.2.2 DELPHI ISSUES                                            
 ===================                                            
 Incorrect number of rows returned on select.                  
   For Windows 3.1, product 5763XC1:  Apply SF29383 or later.  
    See SA48157 for a detailed description.                    
                                                               
*** Please see II09604 for the continuation of this APAR **    

Problem Summary

Problem Conclusion

Temporary Fix

Comments

close                                                          

Circumvention


PTFs Available

Affected Modules

         

Affected Publications

Summary Information

Status............................................ CLOSED CAN
HIPER........................................... No
Component..................................
Failing Module..........................
Reported Release................... R540
Duplicate Of..............................




System i Support

    IBM disclaims all warranties, whether express or implied, including, but not limited to, the implied warranties of merchantability and fitness for a particular purpose. By furnishing this document, IBM grants no licenses to any related patents or copyrights. Copyright © 1996,1997,1998, 1999, 2000, 2001, 2002, 2003, 2004, 2005, 2006, 2007, 2008, 2009, 2010, 2011, 2012 IBM Corporation. Any trademarks and product or brand names referenced in this document are the property of their respective owners. Consult the Terms of use link for trademark information.

Rate this page:

(0 users)Average rating

Document information


More support for:

i family

Software version:

V5R4M0

Operating system(s):

OS/400

Reference #:

II09603

Modified date:

2007-05-01

Translate my page

Machine Translation

Content navigation