If DSN users cannot issue SQL statements

This is a situation in which users communicating with Db2 through the DSN command processor cannot complete transactions, but Db2 is still operational (still responding to commands, or operating normally through other attachments).

  1. If you are a SPUFI user, determine if the SQL statement just issued, and for which you received no response, is one that can take longer than usual to execute.

    For example, a statement that updates 80,000 records undoubtedly takes longer than one that updates one record.

  2. Verify that the application programs are checking SQL return codes properly.

    A return code of +100 is received when work completes successfully.

  3. If the problem is in a batch environment, refer to In a batch environment. If the problem is in a foreground environment, continue below with In a foreground environment.

The TSO attachment facility provides several different tracing mechanisms, only one of which, the DSN trace stream, is mentioned here. For information about other tracing mechanisms, refer to TSO attachment facility traces.

In a foreground environment

  1. Try to terminate the current DSN session. Enter 'END' or Enter 'END' press the ATTENTION key twice.
  2. If the z/OS® generalized trace facility (GTF) is not active, issue the z/OS START GTF command with the USR and TIME=YES options.
  3. Issue the Db2 command -START TRACE(GLOBAL) DEST(GTF).
  4. Allocate a DSNTRACE data set to collect a copy of the DSN trace messages that are going to appear on your terminal.
  5. Restart the DSN session using the DSN command DSN SYSTEM(subsystem-name) TEST(255), where subsystem-name is the site-defined name of the subsystem.
  6. Review the DSN trace messages produced. Find any trace messages containing
    BEFORE . . . =============
AFTER  . . . =============

    These indicate control is being passed between the application and Db2. For an example, see the following figure.

  7. If the last trace stream message contains BEFORE with no corresponding AFTER message following, the problem is in Db2. Follow the steps below:
    1. Verify that dump data sets are available.
    2. Issue the z/OS DUMP command to dump the Db2 address spaces and the address space of the affected TSO user. This generates very large dumps, which can help to diagnose the problem if other methods fail.
  8. If the last trace message does not contain BEFORE, the problem is in the application or the DSN command processor. Follow the steps below:
    1. Verify that the application program is not in error. If necessary, have the operator cancel the TSO users involved in the problem. Request dumps for each user whose DSN session is canceled.
    2. If you see an unending series of messages similar to those shown below, the application program is in a loop. Verify that it is checking the SQL return codes properly. 
        BEFORE SQL CALL ==========
  AFTER SQL CALL ==========
    Figure 1. Example of DSN trace messages. Each pair of BEFORE SQL CALL and AFTER SQL CALL trace messages represents one SQL statement. The BEFORE and AFTER TERMINATE DB2 CALL messages show DSN terminating its connection to Db2.
    DSNET20I  DSNECP13  CIBCORID :H443722 X'00000000' X'00000000'
DSNET20I  DSNECP13  ZINDOUBT :NO X'00000000' X'00000000'
DSNET20I  DSNECP13  BEFORE CREATE THREAD DB2 CALL======R6,CIBRFRB X'00187840' X'00187DA0'
DSNET20I  DSNECP13  AFTER CREATE THREAD DB2 CALL======R6,CIBRFRB X'00187840' X'00187DA0'
DSNET20I  DSNECP13  FOLLOWING ARE FRB FIELD CONTENTS, CIBCTFRM= X'00187DA0' X'00000000'
DSNET20I  DSNECP13  FRBRAL(PTR), FRBRALE(BIN15), FRBFVLE(BIN15) X'00000000' X'00010004'
DSNET20I  DSNECP13  FRBPARM(PTR), FRBPCNT(BIN15) X'00000000' X'00000000'
DSNET20I  DSNECP13  FRBRC1(BIN15), FRBRC2(CHAR4) X'00000000' X'00000000'
DSNET20I  DSNECP13  FRBFBACK PRT(31), FRBRHPC X'00000000' X'00000000'
DSNET20I  DSNECP13  FRBQUAL(BIN15), FRBRSV1(BIN15) X'00000001' X'00000000'
DSNET20I  DSNECP13  CIBPLNID :TSOAP1 X'00000000' X'00000000'
DSNET20I  DSNECP13  ZINDOUBT :NO X'00000000' X'00000000'
DSNET20I  DSNECP13  R6=, CIBCTRTN=X'00187840' X'E2E8D5C3'
DSNET20I  DSNECP13  EXIT DSNECP13 X'00000000' X'00187DA0'
DSNET20I  DSNECP28  AFTER DSNECP13, CIBCTRTN=,CIBCTFRB= X'E2E8D5C3' X'00187DA0'
DSNET20I  DSNECP28  HERE COMES THE FRB <<<<<<<<<<<<<<< CIBRFRB= X'80187DA0' X'00000000'
DSNET20I  DSNECP28  FRBRAL(PTR),    FRBRALE(BIN15), FRBFVLE(BIN15) X'00000000' X'00030001'
DSNET20I  DSNECP28  FRBPARM(PTR),   FRBPCNT(BIN15) X'001AB1E0' X'000000000'
DSNET20I  DSNECP28  FRBRC1(BIN15),  FRBRC2(CHAR4) X'00000000' X'00000000'
DSNET20I  DSNECP28  FRBFBACK:                                               X'00000000' X'00000000'
DSNET20I  DSNECP28  FRBFBACK PTR(31),FRBRHPC X'00000000' X'00000000'     840' X'00187DA0'
DSNET20I  DSNECP28  FRBQUAL(BIN15), FRBRSV1(BIN15) X'00000001' X'00000000'
DSNET20I  DSNECP28  EXIT DSNECP28,CIBRFRB,CIBFRMLI X'80187DA0' X'00000000'
DSNET20I  DSNETRAP  BEFORE SQL CALL=======================FRB,R1== X'80187DA0' X'00187870'
DSNET20I  DSNETRAP  AFTER SQL CALL=====================RC1,FBACK== X'00000000' X'00000000'
DSNET20I  DSNETRAP  BEFORE SQL CALL=======================FRB,R1== X'80187DA0' X'00187870'
DSNET20I  DSNETRAP  AFTER SQL CALL=====================RC1,FBACK== X'00000000' X'00000000'
DSNET20I  DSNETRAP  AFTER LINK GOOD, R15=, R1= X'00000000' X'0018753C'   40' X'00187DA0'
DSNET20I  DSNECP18  ENTER DSNECP18, CIBTRMOP= X'E2E8D5C3' X'00000000'
DSNET20I  DSNECP18  CIBTRMOP NOT BLANK, CIBTRMOP= X'E2E8D5C3' X'00000000'
DSNET20I  DSNECP18  FRB FIELDS FOLLOW (CIBFRB): X'00187DA0' X'00000000'
DSNET20I  DSNECP18  FRBRAL(PTR),    FRBRALE(BIN15), FRBFVLE(BIN15) X'00000000' X'00010001'
DSNET20I  DSNECP18  FRBPARM(PTR),   FRBPCNT(BIN15) X'001873A8' X'00000000'
DSNET20I  DSNECP18  FRBRC1(BIN15),  FRBRC2(CHAR4) X'00000000' X'00000000'
DSNET20I  DSNECP18  FRBFBACK(PTR),  FRBRHPC(BIN32) X'00000000' X'00000000'
DSNET20I  DSNECP18  FRBQUAL(BIN15), FRBRSV1(BIN15) X'00000001' X'00000000'
DSNET20I  DSNECP18  BEFORE TERMINATE DB2 CALL===============TRMOP= X'E2E8D5C3' X'00000000'
DSNET20I  DSNECP18  AFTER TERMINATE DB2 CALL====================== X'00000000' X'00000000'
DSNET20I  DSNECP18  FRB FIELDS FOLLOW (CIBFRB): X'00187DA0' X'00000000'
DSNET20I  DSNECP18  FRBRAL(PTR),    FRBRALE(BIN15), FRBRVLE(BIN15) X'00000000' X'00010001'
  9. If the problem appears to be in the DSN command processor, add WAIT TSOATTACH or LOOP TSOATTACH to the keyword string. If unsure about which is appropriate, build two keyword strings, one with each keyword. Then turn to Searching for known problems and solutions for Db2 for z/OS. 
      (free format):
  5740XYR00 R121 WAIT TSOATTACH
  5740XYR00 R121 LOOP TSOATTACH
  (structured format):
  PIDS/5740XYR00 LVLS/121 TSOATTACH WAIT
  PIDS/5740XYR00 LVLS/121 TSOATTACH LOOP
  10. If the problem appears to be in Db2, add WAIT or LOOP to the keyword string. If unsure about which is appropriate, build two keyword strings, one with each keyword. Then turn to Searching for known problems and solutions for Db2 for z/OS. 
      (free format):
  5740XYR00 R121 WAIT
  5740XYR00 R121 LOOP
  (structured format):
  PIDS/5740XYR00 LVLS/121 WAIT
  PIDS/5740XYR00 LVLS/121 LOOP

In a batch environment

  1. If the z/OS generalized trace facility (GTF) is not active, issue the z/OS command START GTF with the USR and TIME=YES options.
  2. Issue the Db2 command -START TRACE(GLOBAL) DEST(GTF).
  3. Request an z/OS dump of Db2 and the batch job involved in the problem.
  4. Cancel the job.
  5. Resubmit the job with a SYSUDUMP DD card, using the DSN command DSN SYSTEM(subsystem-name)(TEST(255). Replace subsystem-name with the site-defined name of the subsystem.
  6. Let the job run until you think the problem has occurred again. Then have the operator cancel the job with a dump.
  7. Review the trace output. In batch DSN sessions, the trace messages go to the SYSTSPRT data set. Figure 1 provides an example of these trace messages. Find any trace messages similar to 
    BEFORE . . . =============
AFTER  . . . =============
    These indicate control is being passed between the application and Db2.
  8. Verify that the application program is not causing a loop.
    1. Submit the job two or more times and compare the amount of trace message output. Determine if the trace messages seem to continue until the job times out or the operator cancels it.
    2. If necessary, have the operator cancel the TSO users involved. Request dumps for each user whose DSN session is canceled.
    3. Check for an unending sequence of messages similar to those shown below. If this occurs, the application program is causing the loop. Verify that it is checking the SQL return codes properly.
      BEFORE SQL CALL ==========
AFTER SQL CALL ==========
  9. If the problem appears to be in the DSN command processor, add WAIT TSOATTACH or LOOP TSOATTACH to the keyword string. If unsure which is appropriate, build two keyword strings, one with each keyword. Then turn to Searching for known problems and solutions for Db2 for z/OS.
      (free format):
  5740XYR00 R121 WAIT TSOATTACH
  5740XYR00 R121 LOOP TSOATTACH
  (structured format):
  PIDS/5740XYR00 LVLS/121 TSOATTACH WAIT
  PIDS/5740XYR00 LVLS/121 TSOATTACH LOOP
  10. If the problem appears to be in Db2, add WAIT or LOOP to the keyword string. If unsure which is appropriate, build two keyword strings, one with each keyword. Then turn to Searching for known problems and solutions for Db2 for z/OS.
      (free format):
  5740XYR00 R121 WAIT
  5740XYR00 R121 LOOP
  (structured format):
  PIDS/5740XYR00 LVLS/121 WAIT
  PIDS/5740XYR00 LVLS/121 LOOP