1 / 41

Where Do We Start?

Where Do We Start?. When?. Who?. What?. Why?. Where?. Sources of Debug Information CICS Transaction Abends Batch Abend Codes System Codes Message Logs DB2 SQL Codes . How Do We Debug?. The system reports abends in the form Sxxx Uxxxx. The S literally means "System"

Samuel
Download Presentation

Where Do We Start?

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Where Do We Start? When? Who? What? Why? Where?

  2. Sources of Debug Information CICS Transaction Abends Batch Abend Codes System Codes Message Logs DB2 SQL Codes How Do We Debug?

  3. The system reports abends in the form Sxxx Uxxxx. The S literally means "System" The U literally means "User". One of the most common items mentioned in the table is "subscript out of range". This refers to any access to a COBOL array with a subscript <=0 or >n, where n is the number of OCCURS. If the program stores data in the array with a subscript, memory outside of the array can be destroyed; perhaps causing a later 0C1, 0C4, 0C7 or 04E. Batch Abend Codes

  4. SQL Code Basics Where SQL comes from SQLCode vs. SQLState Good & Bad When Should You Check SQL Codes? SQL Code Checking The code The cause Responsible party Corrective actions Common SQL Codes 000 100 -117 -180 & 181 -501 -803 -805 & -818 -811 -904 -911 Where to Go for Help Our Agenda

  5. SQLCA Elements COBOL: 01 SQLCA. 05 SQLCAID PIC X(8). 05 SQLCABC PIC S9(9) COMP-4. 05 SQLCODE PIC S9(9) COMP-4. 05 SQLERRM. 49 SQLERRML PIC S9(4) COMP-4. 49 SQLERRMC PIC X(70). 05 SQLERRP PIC X(8). 05 SQLERRD OCCURS 6 TIMES PIC S9(9) COMP-4. 05 SQLWARN. 10 SQLWARN0 PIC X. 10 SQLWARN1 PIC X. 10 SQLWARN2 PIC X. 10 SQLWARN3 PIC X. 10 SQLWARN4 PIC X. 10 SQLWARN5 PIC X. 10 SQLWARN6 PIC X. 10 SQLWARN7 PIC X. 05 SQLEXT. 10 SQLWARN8 PIC X. 10 SQLWARN9 PIC X. 10 SQLWARNA PIC X. 10 SQLSTATE PIC X(5). An SQLCA is a structure or collection of variables that is updated after each SQL statement executes. An application program that contains executable SQL statements must provide exactly one SQLCA.

  6. Available in V8 Use for Multi Row Operations Use for support long names Use to retrieve additional information Get Diagnostics Use the GET DIAGNOSTICS statement to handle multiple SQL errors that might result from the execution of a single SQL statement. First, check SQLSTATE (or SQLCODE) to determine whether diagnostic information should be retrieved by using GET DIAGNOSTICS.

  7. What Does It Look Like? EXEC SQL BEGIN DECLARE SECTION; long row_count, num_condns, i; long ret_sqlcode, row_num; char ret_sqlstate[6]; ... EXEC SQL END DECLARE SECTION; ... EXEC SQL INSERT INTO DSN8810.ACT (ACTNO, ACTKWD, ACTDESC) VALUES (:hva1, :hva2, :hva3) FOR 10 ROWS NOT ATOMIC CONTINUE ON SQLEXCEPTION; EXEC SQL GET DIAGNOSTICS :row_count = ROW_COUNT, :num_condns = NUMBER; printf("Number of rows inserted = %d\n", row_count); for (i=1; i<=num_condns; i++) { EXEC SQL GET DIAGNOSTICS CONDITION :i :ret_sqlcode = DB2_RETURNED_SQLCODE, :ret_sqlstate = RETURNED_SQLSTATE, :row_num = DB2_ROW_NUMBER; printf("SQLCODE = %d, SQLSTATE = %s, ROW NUMBER = %d\n", ret_sqlcode, ret_sqlstate, row_num); }

  8. SQLCode More specific information Have associated tokens Error Code Resource Type Can point to object Resource Name SQLState Std across whole DB2 family z/OS z/OS SQL Codes vs. SQL State

  9. Good & Bad SQL Codes

  10. Typical SQL Code History

  11. Check SQL Codes (cont.) Misc GET DIAGNOSTICS CALL CONNECT SET Skip SQL Code Checks BEGIN DECLARE SECTION DECLARE STATEMENT DECLARE TABLE END DECLARE SECTION INCLUDE WHENEVER Check SQL Codes Cursors OPEN FETCH CLOSE Basic I/O SELECT INSERT UPDATE DELETE UOW COMMIT ROLLBACK When to Check SQL Codes

  12. Matching SQLCODEs to SQL Commonly Handled  Could occur but not commonly handled

  13. Overview of SQL Calls Before Call • SQL is transformed to COBOL calls in precompile • Host variables loaded before the call • DB2 Call is executed • SQLCODE gives feedback • 0 - OK • <0 - failure • >0 - warning Load Host Variables EXEC SQL ~~~~~ ~~~~~ ~~~~~ END-EXEC Static SQL Call Call Feedback SQLCODE Checks 0 - Successful Call <>0 - Unsuccessful Call

  14. How’s It Done Handle expected codes before call Call UT97894P-CHECK-SQLCODE after every SQL call Catch handled codes after Inconsistent SQL Code Checking Leads To breaks program logic weird program errors can extend debugging time SQL Code Checking SET WS960-HANDLE-NOTFND TO TRUE EXEC SQL ~~~~~~ ~~~~~~ END-EXEC PERFORM UT97894P-CHECK-SQLCODE THRU UT97894P-CHECK-SQLCODE-EXIT IF WS960-R-NOTFND PERFORM ~~~~~~ THRU ~~~~~~-EXIT END-IF

  15. SQL Code Normal SQL Code: 000

  16. Standard SQL Code Checking SELECT SQL Code <>0 ERROR 0 It's All Simple When You Get a Zero

  17. Not Found SQL Code: +100

  18. Fetch Loop OPEN CURSOR SQL Code <>0 ERROR 0 FETCH +100 SQLCode Signal to Exit Loop SQL Code 0 <0 ERROR +100 CLOSE SQL Code <>0 ERROR 0

  19. Mismatch SQLState: 42802

  20. Column Mismatch INSERT INTO TABLE_A VALUES (:I-CLIE ,:T_CREA ,:I_ACCN_PATN ) #1 Table_A I_CLIE T_CREA I_ACCN_PATN #2 Alter Table Add Column T_MODF INSERT INTO TABLE_A VALUES (:I-CLIE ,:T_CREA ,:I_ACCN_PATN ) Table_A I_CLIE T_CREA I_ACCN_PATN T_MODF X #3 With COBOL, This Happens on a Recompile

  21. DB2 Date, Time & Timestamp Errors SQLState: 22007 for both SQL Codes

  22. SQL Code: -180 Valid Formats Leading Zeroes Can Be Omitted

  23. SQL Code: -181 Ranges

  24. Bogus FETCH or CLOSE SQLState: 24501

  25. Missing SQL Code Checking OPEN CURSOR SQL Code <>0 ERROR 0 A Undetected Rollback on the UPDATE Would Cause the Cursor to Be Closed! FETCH SQL Code 0 <0 ERROR UPDATE +100 Missing Check CLOSE SQL Code <>0 ERROR 0

  26. DB2 Duplicate Key Errors SQLState: 23505

  27. Unique Index Elements SET WS960-DUPKEY TO TRUE EXEC SQL UPDATE VRS97100 SET I_MRI_PATN = NEW-I-MRI-PATN WHERE I_CLIE = :RS100-I-CLIE AND I_ACCN_PATN = :RS100-I-ACCN-PATN END-EXEC PERFORM UT97894P-CHECK-SQLCODE THRU UT97894P-CHECK-SQLCODE-EXIT … VRS97100 Unique Indexes XRS97100 I_CLIE I_ACCN_PATN XRS97101 I_MRI There Can Be Multiple Sets of Unique Constraints

  28. DB2 Precompiler Timestamp Errors SQLState: 51002 & 51003

  29. Consistency Tokens Loadlib DBRMlib Token in DBRM & Object Module Must Match

  30. DB2 Multiple Rows Errors SQLState: 21000

  31. If SQLCODE is OK (=0); 1) SELECTS 1 row SQLCODE = 0 Use host variables If SQLCODE fails (<>0); 1) no rows exist SQLCODE = +100 Don’t use host variables! 2) more than 1 row exists SQLCODE = -811 Don’t use host variables! 3) other non zero SQLCODE Don’t use host variables! #1 Singleton SELECT(SELECTS 1 Row & Columns) SET WS960-HANDLE-NOTFND TO TRUE EXEC SQL SELECT I_MRI_PATN ,N_LAST_PATN INTO :RS100-I-MRI-PATN ,:RS100-N-LAST-PATN WHERE I_CLIE = :RS100-I-CLIE AND I_ACCN_PATN = :RS100-I-ACCN-PATN FROM VRS97100 END-EXEC PERFORM UT97894P-CHECK-SQLCODE THRU UT97894P-CHECK-SQLCODE-EXIT IF WS960-R-NORMAL MOVE RS100-I_MRI_PATN TO…. END-IF Only 1 Row Can Be Returned

  32. If SQLCODE is OK (=0); 1) existence of 1 or more rows SQLCODE = 0 If SQLCODE fails (<>0); 1) existence of no rows SQLCODE = +100 2) other failure SQLCODE <0 #2 Existence Checking(The Most Efficient Way) SET WS960-HANDLE-NOTFND TO TRUE EXEC SQL SELECT 1 INTO :WS400-NUMBER FROM VRI97000 WHERE I_CLIE = :RI000-I-CLIE AND I_MRI_PATN = :RI000-I-MRI-PATN FETCH FIRST ROW ONLY END-EXEC PERFORM UT97894P-CHECK-SQLCODE THRU UT97894P-CHECK-SQLCODE-EXIT IF WS960-R-NOTFND THEN …. END-IF This will NEVER return a -811 SQLCODE!

  33. If SQLCODE is OK (=0); 1) existence of >0 rows SQLCODE = 0 INDICATOR-VAR >= 0 If SQLCODE fails (<>0); 1) existence of no rows SQLCODE = +100 INDICATOR-VAR < 0 2) other failure SQLCODE < 0 #3 Counting Rows SET WS960-HANDLE-NOTFND TO TRUE EXEC SQL SELECT COUNT(*) INTO :WS400-NUMB :WS400-INDICATOR-VARIABLE FROM VCP97160 WHERE I_CLIE = :CP160-I-CLIE AND I_CODE = :CP160-I-CODE END-EXEC PERFORM UT97894P-CHECK-SQLCODE THRU UT97894P-CHECK-SQLCODE-EXIT IF (WS960-R-NORMAL AND WS400-INDICATOR-VARIABLE >= 0) MOVE WS400-NUMB TO …. END-IF This will NEVER return a -811 SQLCODE!

  34. If SQLCODE is OK (=0); 1) existence of 1 row SQLCODE = 0 If SQLCODE fails (<>0); 1) existence of no rows SQLCODE = +100 2) existence of +1 rows SQLCODE=-811 3) other failure SQLCODE < 0 #4 Checking for MULTROWswith a Singleton SELECT SET WS960-HANDLE-NOTFND TO TRUE SET WS960-HANDLE-MULTROW TO TRUE EXEC SQL SELECT 1 INTO :WS400-NUMB FROM VCP97160 WHERE I_CLIE = :CP160-I-CLIE AND I_CODE = :CP160-I-CODE END-EXEC PERFORM UT97894P-CHECK-SQLCODE THRU UT97894P-CHECK-SQLCODE-EXIT EVALUATE TRUE WHEN WS960-R-NOTFND …. WHEN WS960-R-MULTROW …. END-EVALUATE

  35. If SQLCODE is OK (=0); 1) SELECTS 1 row SQLCODE = 0 Use host variables If SQLCODE fails (<>0); 1) no rows exist SQLCODE = +100 Don’t use host variables! 2) other non zero SQLCODE Don’t use host variables! #5 Returning a ValueFrom Any Row SET WS960-HANDLE-NOTFND TO TRUE EXEC SQL SELECT C_N_STAN INTO :CP270-C-N-STAN WHERE I_CLIE = :CP270-I-CLIE AND I_N_STAN = :CP270-I-N-STAN AND I_N_STAN_ASSC = :CP270-I-N-STAN-ASSC FROM VRS97100 FETCH FIRST ROW ONLY END-EXEC PERFORM UT97894P-CHECK-SQLCODE THRU UT97894P-CHECK-SQLCODE-EXIT IF WS960-R-NORMAL …. END-IF Any Row in the Set Can Be Returned

  36. DB2 Unavailable Resources SQLState: 57011

  37. Common Resource Type Codes

  38. DB2 Deadlocks & Timeouts SQLState: 40001

  39. Area Experts DB2 Messages DB2 Codes DB2 Web Site www.ibm.com Other Technical Resources

  40. Questions

More Related