IBM DB2 for i: Tips

ODBC troubleshooting

Unable to connect to the AS/400

Understanding the database server program

Each Client Access function communicates with a program that runs on the AS/400. This program is referred to as the "host server" program. Several host programs are used for database serving depending on the type of connectivity. For example:

The Client Access for Windows 95 product is the first member of the Client Access family that can run across multiple protocol stacks: SNA (or 802.2); IPX (novell); and TCP/IP. When troubleshooting this client, you must be aware of the type of connectivity. All other clients (such as the windows 3.1 client) use only SNA. ANYNET should be considered SNA.

Under normal conditions these programs are evoked transparently; ie, the user needs to take no action except to verify the proper subsystems and communication protocols are running. See SC41-3740 OS/400 Server Concepts and Administration for details on administration of host server jobs.

Checking server status (TCP/IP and IPX connections only)

The Client Access for Windows 95 product has a special command to verify status of host servers:
CWBPING systemname protocol
where systemname is the name of the AS/400 system
and protocol is IP or IPX, the default is IP.

The command should return the following:

pinging server Port Mapper successful
pinging server as-central successful
pinging server as-database successful
pinging server as-dtaq successful
pinging server as-file successful
pinging server as-netprt successful
pinging server as-rmtcmd successful
pinging server as-signon successful

If the servers are not active or you are using a SNA connection then continue with the next section.

Verify the proper subsystems are running

TCP/IP and IPX connected ODBC jobs (QZDASOINIT) will run in the QSERVER subsystem. SNA connected ODBC jobs (QZDAINIT) will normally run in QSERVER. Verify that this subsystem is running. The Client Access for Extended DOS router and many OEM SNA connections (such as Netware for SAA) can only use the mode QPCSUPP and will therefore run the ODBC job in QCMN.

Because of changes made to subsystem descriptions in V3R1 and later, the QSERVER subsystem may need to be manually started. To do this, simply issue the following command:
STRSBS QSERVER

To have the subsystem start automatically at IPL, then modify the AS/400 IPL Start up procedure (the default is QSYS/QSTRUP) to include the STRSBS QSERVER command.

In addition to subsystem QSERVER, subsystem QSYSWRK must be running for TCP/IP and IPX connections.

Verify that the proper prestart jobs are running.

IBM ships the QSERVER subsystem configured to use prestart jobs to improve performance at job initialization/start up. When prestart jobs are configured in the subsystem, the job MUST be active to connect. The prestart job used depends on the type of connection:

To verify a prestart job is running:
WRKSBSJOB QSERVER

The appropriate prestart jobs should be active:

Job         User      Type   -----Status-----
QZDAINIT    QUSER     PJ     ACTIVE       (SNA connection)
QZDASOINIT  QUSER     PJ     ACTIVE       (socket connection)
QZDASRVSD   QUSER     PJ     ACTIVE       (socket connection)

Prestart jobs do not display in WRKACTJOB unless a connection is already active. You must use F14 - Include from the WRKACTJOB panel for prestart jobs to display.

Further TCP/IP and IPX considerations.

Verify that TCP/IP or IPX is started with the following command:
NETSTAT *CNN

Use the commands STRTCP or STRIPX to start the desired protocol if it is not running.

Verify the necessary daemons are running by browsing the information returned from the NETSTAT *CNN command:

Remote           Remote     Local
Address          Port       Port       Idle Time  State
*                *          as-cent >  000:09:31  Listen
*                *          as-signon  000:09:41  Listen
*                *          as-svrmap  002:57:45  Listen
*                *          as-data >  002:57:45  Listen

Use the command STRHOSTSVR SERVER(*ALL) to start them if necessary.

The PC locates the socket used by the database server by connecting to the server mapper socket. It retrieves the socket used by as-database. It then connects to the proper socket which is being monitored by the file server daemon, QZDASRVSD. The server daemon will attach the client's connection to a QZDASOINIT prestart job in QSERVER. After validating the user profile and password and swapping the user profile into the prestart job, the job will run similar to the QPWFSERV jobs of an SNA connection. If this is the first connection made to the as/400 for this PC, then two other servers are used: Central server for licensing and Signon server for userid/password validation.

Verify that the host server program product is installed.

The Database Server is installed as part of the operating system. If all PCs are still unable to connect to the server or the QSERVER subsystem is not on the system, then verify that product xxxxSS1, option Host Servers is installed where xxxx is the OS/400 lpp (license program product) for the version current version of OS/400.

Identifying the ODBC job and joblog

How to find your job

This can sometimes be challenging as WRKACTJOB shows the prestart jobs as all running under QUSER. You must display the joblog to find the user profile the job is actively running under.

For a SNA connection, there is an easier method. Use Work with Configuration status on the PC's APPC device. All jobs allocated for the device will appear there. To do this, type the following command:
WRKCFGSTS CFGTYPE(*DEV) CFGD(LOCNAME)

Where LOCNAME is the Local Location name of your PC. To find this value, look up the LOCALLUNAME parameter. This parameter is part of the SNA router configuration. For the Client Access for Windows 3.1 router it is contained in the WINDOWS\NSD.INI file. Keep in mind that the AS/400 may add a '0x' to the end of the name if it detects a damaged controller.

The following will be displayed:
QSERVER ACTIVE/TARGET QZDAINIT QUSER 123456

(Where 123456 is your Job ID.)

or if another mode description is used:
QPCSUPP ACTIVE/TARGET LOCNAME USERID 123456

(Where LOCNAME is your Local Location name, USERID, is the user id that you used to sign-on to the router with, and 123456 is your Job ID.)

For a TCP/IP or IPX connection, the following command may help locate the ODBC job:
WRKOBJLCK OBJ(userid) OBJTYPE(*USRPRF)

"userid" is your user profile used for the ODBC connection.

How to generate a joblog

Some internal errors cause the job to immediately end. To isolate these types of errors, the database server job needs modified so that a joblog is generated. A CHGJOB command can be used to modify an existing job or the job description can be modified to change all new jobs. To modify the default job description enter the following command prior to recreating the error:
CHGJOBD JOBD(QDFTJOBD) LOG(4 00 *SECLVL)

When finished, execute this command to change it back:
CHGJOBD JOBD(QDFTJOBD) LOG(4 00 *NOLIST)

If the job description is not changed back other jobs on the system will be forcing joblogs to be written and system storage will fill up rapidly.

How to find the joblog

Type the following command and prompt for parameters using F4:
CHGPRTF FILE(QPJOBLOG)

Press F10 and Page down until to a field called Spooled Output Queue. This is the OUTQ that the joblog will be written to. By default it is QEZJOBLOG in library QUSRSYS. Record the file and library name of the output queue, and then run the following command to work with all of the entries in the output queue:
WRKOUTQ QEZJOBLOG

Press F16 to move to the bottom of the list.

An alternative way of finding the joblog is to use the WRKSPLF command. If running under QSERVER, then the USERID is always QUSER. If running under another subsystem, then the USERID will be the one used for the router sign on. The following command can be used:
WRKSPLF USERID

Where USERID is QUSER or your USERID.

Common errors

Connection errors

Undefined dynalink

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 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 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)

Ivalid security APPC RC0x32 or RC0x37

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 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 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.

APPC RC0x85 or RC0x87

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

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.

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. See APAR SA49616 for detailed information. SA49616 specifically addresses coexistence issues with COGNOS Impromptu. It also provides general information on commitment control and how it relates to journaling.

Application specific errors

Conformance errors with Microsoft Access

DELPHI ISSUES

Incorrect number of rows returned on select.
Apply SF29383 or later. See SA48157 for a detailed description.

Problems with stored procedures

Internal Driver Error
MSGSQL0901 SQL system error

This error is generated when using output or input/output parameters and the server is unable to find a valid DECLARE or CREATE procedure describing the type of parameters.

If using CREATE PROCEDURE then try running a query over QSYS2/SYSPROCS to verify the create procedure was run correctly. SPECIFIC_SCHEMA and ROUTINE_SCHEMA must match the library used on the call in the PC application. EXTERNAL_NAME must resolve to the actual program name. The parameter descriptions must also be correct.

If using DECLARE PROCEDURE then you must use extended dynamic support to return output or input output parameters. The call of the stored procedure must be stored in the active package. Use PRTSQLINF on the package to verify it's contents. DELETE THE SQL PACKAGE AFTER MAKING ANY CHANGE TO DECLARE PROCEDURE. Parameter descriptions are stored in the package as part of the call entry, not the declare entry.

DECLARE PROCEDURE takes precedence over CREATE PROCEDURE. Delete the package if the problem persists.

No data returned on OUTPUT and INPUT_OUTPUT parameters

MSGSQL0501 Cursor CRSR000x not open
No data returned on Result set

You must specify the compile option ACTGRP(*CALLER) and not *NEW to return data.

Incorrect output and unpredictable errors

The ODBC driver manager and client access ODBC driver should be at matching versions of the ODBC specification or unpredictable results may occur. Applications do not call the client access ODBC driver directly. The application loads the ODBC driver manager, ODBC.DLL. This in turn loads the client access ODBC dll. API calls pass through the driver manager and can be altered by the driver manager. Multiple errors may occur if ODBC.DLL is downlevel from the client access ODBC driver. The following is a list of Client Access products and their corresponding ODBC level:

-  Client Access for Windows R310 (only):     ODBC 1.0
-  Client Access for Windows R311 and later:  ODBC 2.1C
-  Client Access Optimized for OS/2r:         ODBC 1.0
-  Client Access for Windows 95:              ODBC 2.5

MSGPWS0005 - Error occurred in the OS/400 database server code.

This error could be caused by damaged or missing files that the database server needs to operate. This can be caused by restoring licensed product 5763SS1 Option 12 (Host Servers) from a save tape built prior to applying PTF SF23488. To fix these files, do the following on the AS/400 command line:

Hexidecimal data instead of ASCII characters

The Data Source Name (DSN) is configured to the ForceTranslation default of 0 (No translation). The AS/400 attaches a language identifier or CCSID to files, tables and even fields (columns). CCSID 65535 is a generic code that identifies a file or field as being language independent: i.e. hexadecimal data. By definition, no translation is performed by the ODBC driver. If you do not wish to update the CCSID of the file, then translation to the default ascii code page can be forced.

General protection fault

Windows protection violations are usually due to memory mismanagement or due to a program error. To determine which one it is, remove any Memory Managers (e.g. EMM383.EXE) from CONFIG.SYS and run Windows with the /D:FSVX parameter to isolate any obvious memory problems. If unable to correct the problem by removing TSR's (Terminate and Stay Resident programs) or device drivers, then contact the manufacturer of the application for assistance.

Gathering information for IBM support

When opening a problem record to IBM Support please have the following information available when you place the call.

The AS/400 cumulative PTF level

Issue the display PTF command on an AS/400 terminal emulation command line:
DSPPTF

Record the first PTF ID in the list. It will have the format Tzxxyyy where xx is the year, yyy the julian date and z is either L or C.

The OS/400 version

This can also be found from the DSPPTF screen. Record the Release field at the top of the screen.

The version of the client access ODBC driver.

The version of the ODBC driver manager.

The version and type of communication software used.

This might include:

Communication protocols and drivers

Communication adapter

The PC application

Name and version of the PC application.

Error description

A description of what you were attempting when the failure occurred. If possible, the steps needed to recreate the problem.

ODBC joblog

The QZDAINIT joblog if it was produced. See above for details on how to generate and find your joblog.

VLOGs

For internal server errors, service personnel may request VLOGs generated at the time the error. These VLOGs have a major code of 0700 and a minor code of either F230 or F299.

Diagnostic and performance tools

Error Message Help

All of the Client Access error messages can be found either on the AS/400 or in a Client Access/400 help. For errors that begin with SQL, use the following OS/400 command to view the message text:
DSPMSGD RANGE(SQLxxxx) MSGF(QSQLMSG)

For error messages that begin with IWS or PWS, use the following OS/400 command:
DSPMSGD RANGE(ZZZxxxx) MSGF(QIWS/QIWSMSG)
where ZZZ is IWS or PWS.

For APPC errors, see the .Client Access/400 for Windows 3.1 API and Technical Reference SC41-3531.

Microsoft ODBC SDK trace

The ODBC Spy utility which is provided with the Microsoft ODBC SDK (Software Development Kit) is an excellent source for debugging ODBC applications and drivers. You can purchase it from Microsoft.

Microsoft ODBC administrator version 2 API trace

ODBC drivers at the 2.0 or later level have a trace utility built into the Microsoft ODBC Administrator. Selecting a checkbox option in the Administration utility (ODBCADM.EXE) traces ODBC API calls. It does not show as much information as the ODBC Spy utility.

Application trace programs

Many applications have their own built-in ODBC tracing facility (such as Microsoft Access and Powersoft's PowerBuilder).

Communications trace for client access for Windows 3.1x

The Networking Services Trace tool (NSTRACE.EXE) can be found on the Tools folder (QDLS\QIWSTOOL\IWSTOOL.EXE). This is an invaluable tool for tracing communication problems as well as ODBC problems, if you do not have any of the above trace tools. If you format this trace with the default formatting options, a timestamp will be placed in each frame. This will aid you in any performance-related debugging you may want to do.

Netware for SAA 2.0 (NetSoft) trace

Similar to the CA/400 trace, this also does a good job of tracing communication related errors and ODBC problems. This trace tool is built-in to the NSROUTER.EXE program.

CA/400 extended DOS router trace

Though not as nicely formatted, this trace program allows you to trace all communications from the Router to the AS/400. You can find it in the Tools folder.

AS/400 communications trace

The OS/400 communications trace facility will trace any communications type that has a line description (Token-Ring, ethernet, and SDLC) and formats it to the controller level. For TCP/IP connections, the trace can be formatted for a particular IP address.

This is also a great tool for isolating communication errors and ODBC problems. You can get to this by issuing the STRSST command at the OS/400 command-line. The default formatting options record a relative timestamp next to each frame. This is a useful aid for diagnosing where a performance delay is occurring.

AS/400 job traces

The OS/400 job trace can help isolate most host problems and many performance issues. A service job must first be started on the job to be traced. Locate the fully qualified job name of the ODBC job (see How to find your job). From any 5250 emulation session, start a service job on this ODBC job by using the STRSRVJOB command. Then choose one of two traces depending on the information needed.

AS/400 performance tools

AS/400 performance toolkit provides reports and utilities that can be used for in depth analysis of your application performance. Information of CPU utilization, Disk arm utilization, memory paging and much more is provided. Although the collection of performance data is built into the base operating system, you will need the separate license program Performance Tools/400 to analyze the results.

Dr. Watson

Microsoft Windows 3.1 comes with a utility called Dr. Watson (DRWATSON.EXE) which can help determine who is causing General Protection Faults. To use this tool, simply run DRWATSON.EXE from your WINDOWS directory by using Program Manager's FILE/RUN or File Manager. To get the most out of it, add the following entries to your SYSTEM.INI:
Dr. Watson
DisLen=8
DisStack=20
Showinfo=disassembly errorlog locals paramlog registers stack
SkipInfo=modules tasks 32bitregs information
TrapZero=1

When Dr. Watson detects a fault, it will generate a file called DRWATSON.LOG in your WINDOWS directory.

Microsoft diagnostics (MSD.EXE)

The Microsoft Diagnostics tool can help you figure out if you have any memory conflicts and will print-out most of the information that IBM Support Line needs for diagnosing your problem. This tool comes with many versions of Microsoft DOS and Windows.

IBM QCONFIG.EXE

QCONFIG is provided with IBM PC-DOS. It can be used to gather system information in detail. To use it, type:
QCONFIG > QCONFIG.OUT

A file called QCONFIG.OUT will be created in your current directory. You can then PRINT, TYPE or EDIT this file.

AS/400 job log

For most ODBC problems, the Job log is crucial for determining where a problem resides. See the steps provided earlier in this appendix for instructions on getting a Job log.

QPTFIDX

Client Access manages itself by providing an index of PTF's in each directory where it resides. You can find out what PTF level you are at by looking for files called QPTFIDX in your IBM product directories on your PC and on the Shared Folder. It is an index of modules and Temporarily Applied PTF's. Permanently applied PTF's do not show up in this list.

Security

Refer to informational apars II09333 and II09334 for a discussion of ODBC and Client Access security tips and issues.

References

SC41-3740   OS/400 Server Concepts and Administration
SG24-4526   AS/400 Client/Server Performance using the
                         Windows 3.1 Client (redbook)
SC41-3533   Client Access Windows 3.1 Client for OS/400
                         ODBC User's Guide
SG24-4748   Inside AS/400 Client Access for Windows 95
GG24-4249   DATABASE 2/400 Advanced Database Functions
                         (rebook)
SC41-4611   DB2 for OS/400 SQL Programming
SC41-4612   DB2 for OS/400 SQL Reference

Contact IBM

Browse Power Systems

Next generation applications for big data and analytics and cognitive computing are providing unprecedented insights into opportunities, threats and efficiencies. IBM Power Systems is at the forefront of delivering solutions to gain faster insights from analyzing both structured information and unstructured big data. With the secure, flexible and open platform of IBM Power Systems plus solutions and software, organizations can outpace their competitors by delivering faster services, providing differentiated offerings and turning operational cost into investment opportunity.

To draw insights and make better decisions, businesses rely on the secure, flexible and open platform of IBM Power Systems. Built with the first processor designed for big data workloads, the design of Power Systems combines the computing power, memory bandwidth and I/O in ways that are easier to consume and manage, building on strong resiliency, availability and security.

IBM Power Systems deliver flexibility and choice of operating systems to enable your business to support the next generation applications for big data and analytics and cognitive computing that are transforming how organizations work today. Whether running 1, 2, or all 3 - coupled with PowerVM, they maximize the benefit of Power Systems in your business.

Transform your business with Systems Software that enables virtualization, high availability, flexibility, security and compliance on Power Systems™. IBM’s integrated approach to developing Systems and Systems Software together delivers optimized results with Power Systems.

As an open innovation platform, Power Systems is optimized for big data and analytics performance and to deliver scale-out economics and security for the cloud. IBM and IBM Business Partner solutions exploit key capabilities in IBM Power Systems.

Over the last five years thousands of clients have migrated to IBM Power Systems. Learn how Power Systems has helped them support next generation applications for big data and analytics and cognitive computing on an open platform for choice while improving business performance, reducing risk, and establishing a platform for growth.

Additional information