IBM Support

Handling Inquiry Messages in Database Server Jobs

Troubleshooting


Problem

This document lists some of the problems that might occur in database host server jobs (QZDASOINIT) due to inquiry messages. It discusses several options on how the inquiry messages can be handled.

Resolving The Problem

Overview

The database server clients such as IBM i Access Client Solutions "Run SQL Scripts", ODBC, and JDBC applications might perform an action that generates an IBM i inquiry message. Inquiry messages can leave the job in a Message Wait (MSGW) state until a reply is received or automatically reply with a response that is not what the application requires. The client application might appear to hang when the job goes into MSGW state or report an error when the response is not correct.

How IBM i handles an inquiry message depends on the Inquiry Message Reply (INQMSGRPY) job attribute. The possible values are:
*DFT Default. The system automatically answers the inquiry message with the default message reply value.
*RQD Required. The system requires that the message be responded to. This places the job into a message wait state until a user manually responds to the message.
*SYSRPLY The system reply list is checked to see whether there is an entry for any inquiry message issued as a result of running this job. If a match occurs, the reply value in that entry is used. If no entry exists for that inquiry message, a reply is required.
The default configuration shipped with the operating system causes ODBC jobs to answer inquiry messages with the default reply (*DFT).

This document lists some of the common inquiry messages that ODBC might generate and suggestions on how to handle them.

Common ODBC-Generated Inquiry Messages

Running the Alter Table Statement

The SQL Statement ALTER TABLE with a DROP clause might fail when it is run from ODBC. The following messages appear in the job log:

CPD32CC - Change to field &3 may result in data loss.
CPD32CE - Change of file &1 in &2 canceled.
CPA32B2 - Message text . . . . . . . . : Change of file &1 may cause data to be lost. (C I)

Inserting Rows of Data

If an ODBC application is inserting data into a file by using the INSERT statement, it might encounter errors due to the Member Size attribute of the file or table. The following messages might be encountered:

CPA5305 - Record not added. Member xxxxxx is full (C I)
SQL0904 - Resource Limit Exceeded (if the reply is Cancel)

The simple resolution to this problem is use the CHGPF SIZE(*NOMAX) command to avoid the message.

Setting the INQMSGRPY Parameter

The Inquiry Message Reply job attribute is set when the prestart job is started as follows. First, the system checks the job description specified on the prestart job entry in the subsystem description. If this value is set to an actual job description, as it is in V5R3 and later releases of i5/OS, the INQMSGRPY value in the job description is used. If the default value is *USRPRF, then the user profile on the prestart job entry has to be checked by using the DSPUSRPRF command to see which job description is in use. The default user profile for this value is QUSER user profile. The default job description setting for QUSER is QGPL/QDFTJOBD. In turn, the default value for INQMSGRPY parameter in QDFTJOBD is *DFT.

Note: The INQMSGRPY parameter is taken from the user profile of the prestart job (QUSER), not the user profile that connects to the job in ODBC (the user profile being serviced).

The INQMSGRPY value can also be overridden by using a CHGJOB command. The CHGJOB command can be run in an exit program or the application itself might issue the CHGJOB command.

Resolution

There are several different ways to handle the inquiry messages generated by the database server program. Factors to consider when picking a solution include the following:
  • The scope of the reply value (system-wide, all database server jobs, per application or per user);
  • If the application can be modified;
  • If an exit program can be used.
Each method described previously involves setting the database server jobs INQMSGRPY entry to a specific value. There are several ways to change the inquiry message reply value for a database server job.
  1. Changing INQMSGRPY for all database server jobs

    One way of changing the Inquiry message reply value that affects all database server users but not other applications are to create a new job description and use this job description in the two prestart jobs.

    Create a new job description (Note, QDFTSVR is the default job description used by the database host server since V5R4, before that release, the prestart job entry used the job description specified by the user profile, which normally resolves to QDFTJOBD):
    CRTDUPOBJ OBJ(QDFTSVR) FROMLIB(QGPL) OBJTYPE(*JOBD) +
    TOLIB(mylib) NEWOBJ(TESTJOBD)

    Set the job descriptions inquiry message reply to the wanted value (*SYSRPLY, *DFT, or *RQD):
    CHGJOBD JOBD(mylib/TESTJOBD) INQMSGRPY(*SYSRPYL)

    Change the subsystem's prestart job entry
    CHGPJE SBSD(QUSRWRK) PGM(QZDASOINIT) +
    JOBD(MYLIB/TESTJOBD) /* TCP/IP users */
    CHGPJE SBSD(QUSRWRK) PGM(QZDASSINIT) +
    JOBD(MYLIB/TESTJOBD) /* SSL users */
    CHGPJE SBSD(QSERVER) PGM(QZDAINIT) +
    JOBD(MYLIB/TESTJOBD) /* SNA users */

    The change will not go into effect until all existing prestart jobs are ended. To end and restart all jobs:
    ENDPJ SBS(QUSRWRK) PGM(QZDASOINIT)
    ENDPJ SBS(QUSRWRK) PGM(QZDASSINIT)
    ENDPJ SBS(QSERVER) PGM(QZDAINIT)
    STRPJ SBS(QUSRWRK) PGM(QZDASOINIT)
    STRPJ SBS(QUSRWRK) PGM(QZDASSINIT)
    STRPJ SBS(QSERVER) PGM(QZDAINIT)
  2. Change the INQMSGRPY in a user exit
    Another method that is potentially more selective is to issue a CHGJOB command from an exit program. To avoid adding extra processing to the application use the QIBM_QZDA_INIT exit point. This exit point is called only once when the client connects to the database server job.

    For more information about user exit programs, see the IBM i Knowledge Center.
  3. Change the INQMSGRPY from the application
    The most selective method of applying the change is for the application to issue the CHGJOB command. An example of calling CHGJOB from the application as a stored procedure follows:

    SQLExecDirect(hStmt,
    "CALL QSYS.QCMDEXC('CHGJOB INQMSGRPY(*SYSRPYL)', 0000000026.00000)",
    SQL_NTS);
It might be necessary to use a combination of these steps if an application is written to change the job back to using the default value for inquiry message replies. Testing of the "Run SQL Scripts" function from IBM i Access Client Solutions revealed that it was changing the INQMSGRPY to *DFT, which negated the effect of setting the job description to use the system reply list. To overcome this, the following command was added to the beginning of the script: CALL QSYS.QCMDEXC ('CHGJOB INQMSGRPY(*SYSRPYL)', 0000000026.00000);

Once you changed the job so that it will check the system reply list, you need to add the response for the message you want to be automatically responded to. Here is an example of adding the reply list entry for message CPA32B2. Note I picked the Sequence Number of 17, just because its unlikely a 17 already exists. If it does, find an unused sequence number:
ADDRPYLE SEQNBR(17) MSGID(CPA32B2) RPY('I')


Note: Some functions such as the Schemas table definition utility will attempt to change the reply list entry on behalf of the user when the user responds affirmatively when prompted to make a change to the table. In order for that functionality to work, the user must be authorized to the CHGRPLE command in IBM i.

References

Information Center:

http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/index.jsp

[{"Product":{"code":"SWG60","label":"IBM i"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Host Servers","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"Version Independent","Edition":"","Line of Business":{"code":"LOB57","label":"Power"}}]

Historical Number

9331817

Document Information

Modified date:
31 December 2019

UID

nas8N1000052

Page Feedback