Where Do We Start How Do We Debug

Where Do We Start?

How Do We Debug? • Sources of Debug Information – – – CICS Transaction Abends Batch Abend Codes System Codes Message Logs DB 2 SQL Codes

Batch Abend Codes – One of the most common – The system reports items mentioned in the abends in the form Sxxx table is "subscript out of Uxxxx. range". • The S literally means "System" • The U literally means "User". • 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 0 C 1, 0 C 4, 0 C 7 or 04 E.

Our Agenda • 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

SQLCA Elements COBOL: 01 SQLCA. 05 SQLCAID PIC X(8). 05 SQLCABC PIC S 9(9) COMP-4. 05 SQLCODE PIC S 9(9) COMP-4. 05 SQLERRM. 49 SQLERRML PIC S 9(4) COMP-4. 49 SQLERRMC PIC X(70). 05 SQLERRP PIC X(8). 05 SQLERRD OCCURS 6 TIMES PIC S 9(9) COMP-4. 05 SQLWARN. 10 SQLWARN 0 PIC X. 10 SQLWARN 1 PIC X. 10 SQLWARN 2 PIC X. 10 SQLWARN 3 PIC X. 10 SQLWARN 4 PIC X. 10 SQLWARN 5 PIC X. An SQLCA is a structure or 10 SQLWARN 6 PIC X. collection of variables that is updated 10 SQLWARN 7 PIC X. after each SQL statement executes. 05 SQLEXT. An application program that contains 10 SQLWARN 8 PIC X. executable SQL statements must 10 SQLWARN 9 PIC X. 10 SQLWARNA PIC X. provide exactly one SQLCA. 10 SQLSTATE PIC X(5).

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. • Available in V 8 • Use for Multi Row Operations • Use for support long names • Use to retrieve additional information

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 DSN 8810. ACT (ACTNO, ACTKWD, ACTDESC) VALUES (: hva 1, : hva 2, : hva 3) FOR 10 ROWS NOT ATOMIC CONTINUE ON SQLEXCEPTION; EXEC SQL GET DIAGNOSTICS : row_count = ROW_COUNT, : num_condns = NUMBER; printf("Number of rows inserted = %dn", row_count); for (i=1; i<=num_condns; i++) { EXEC SQL GET DIAGNOSTICS CONDITION : i : ret_sqlcode = DB 2_RETURNED_SQLCODE, : ret_sqlstate = RETURNED_SQLSTATE, : row_num = DB 2_ROW_NUMBER; printf("SQLCODE = %d, SQLSTATE = %s, ROW NUMBER = %dn", ret_sqlcode, ret_sqlstate, row_num); }

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

Good & Bad SQL Codes If SQLCODE = 0 Execution Was Successful If SQLCODE > 0 Execution Was Successful With a Warning If SQLCODE < 0 Execution Was Not Successful

Typical SQL Code History

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

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

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

SQL Code Checking – How’s It Done • Handle expected codes before call • Call UT 97894 P-CHECKSQLCODE after every SQL call • Catch handled codes after – Inconsistent SQL Code Checking Leads To • breaks program logic • weird program errors • can extend debugging time SET WS 960 -HANDLE-NOTFND TO TRUE EXEC SQL ~~~~~~ END-EXEC PERFORM UT 97894 P-CHECK-SQLCODE THRU UT 97894 P-CHECK-SQLCODE-EXIT IF WS 960 -R-NOTFND PERFORM ~~~~~~ THRU ~~~~~~-EXIT END-IF

SQL Code Normal

Standard SQL Code Checking SELECT SQL Code 0 <>0 ERROR

Not Found

Fetch Loop OPEN CURSOR SQL Code <>0 ERROR 0 FETCH 0 SQL Code <0 ERROR +100 CLOSE SQL Code 0 <>0 ERROR

Mismatch SQLState: 42802

Column Mismatch INSERT INTO TABLE_A VALUES (: I-CLIE , : T_CREA , : I_ACCN_PATN ) #1 #2 INSERT INTO TABLE_A VALUES (: I-CLIE , : T_CREA , : I_ACCN_PATN ) X Table_A I_CLIE T_CREA I_ACCN_PATN Alter Table Add Column T_MODF Table_A #3 I_CLIE T_CREA I_ACCN_PATN T_MODF

DB 2 Date, Time & Timestamp Errors SQLState: 22007 for both SQL Codes

SQL Code: -180 Valid Formats Timestamp yyyy-mm-dd-hh-mm-ss-msmsms yyyy-mm-dd-hh-mm-ss Date mm/dd/yyyy-mm-dd dd. mm. yyyy Time hh: mm: ss hh: mm hh. mm. ss hh. mm hh: mm AM or hh: mm PM

SQL Code: -181 Ranges Component Year Month Day Hour Minute Second Microsecond Valid Range 0001 - 9999 1 – 12 1 – 31 (depends upon month & year) 0 - 24 0 – 59 0 - 9999

Bogus FETCH or CLOSE SQLState: 24501

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 UPDATE 0 SQL Code <0 ERROR +100 Missing Check CLOSE SQL Code 0 <>0 ERROR

DB 2 Duplicate Key Errors SQLState: 23505

Unique Index Elements SET WS 960 -DUPKEY TO TRUE EXEC SQL UPDATE VRS 97100 SET I_MRI_PATN = NEW-I-MRI-PATN WHERE I_CLIE = : RS 100 -I-CLIE AND I_ACCN_PATN = : RS 100 -I-ACCN-PATN END-EXEC PERFORM UT 97894 P-CHECK-SQLCODE THRU UT 97894 P-CHECK-SQLCODE-EXIT … VRS 97100 Unique Indexes XRS 97100 I_CLIE I_ACCN_PATN XRS 97101 I_MRI

DB 2 Precompiler Timestamp Errors SQLState: 51002 & 51003

Consistency Tokens DBRMlib Loadlib

DB 2 Multiple Rows Errors SQLState: 21000

#1 Singleton SELECT (SELECTS 1 Row & Columns) SET WS 960 -HANDLE-NOTFND TO TRUE – 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! EXEC SQL SELECT I_MRI_PATN , N_LAST_PATN INTO : RS 100 -I-MRI-PATN , : RS 100 -N-LAST-PATN WHERE I_CLIE = : RS 100 -I-CLIE AND I_ACCN_PATN = : RS 100 -I-ACCN-PATN FROM VRS 97100 END-EXEC PERFORM UT 97894 P-CHECK-SQLCODE THRU UT 97894 P-CHECK-SQLCODE-EXIT IF WS 960 -R-NORMAL MOVE RS 100 -I_MRI_PATN TO…. END-IF

#2 Existence Checking (The Most Efficient Way) SET WS 960 -HANDLE-NOTFND TO TRUE EXEC SQL SELECT 1 INTO : WS 400 -NUMBER FROM VRI 97000 WHERE I_CLIE = : RI 000 -I-CLIE AND I_MRI_PATN = : RI 000 -I-MRI-PATN FETCH FIRST ROW ONLY END-EXEC PERFORM UT 97894 P-CHECK-SQLCODE THRU UT 97894 P-CHECK-SQLCODE-EXIT IF WS 960 -R-NOTFND THEN …. END-IF – 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

#3 Counting Rows – 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 SET WS 960 -HANDLE-NOTFND TO TRUE EXEC SQL SELECT COUNT(*) INTO : WS 400 -NUMB : WS 400 -INDICATOR-VARIABLE FROM VCP 97160 WHERE I_CLIE = : CP 160 -I-CLIE AND I_CODE = : CP 160 -I-CODE END-EXEC PERFORM UT 97894 P-CHECK-SQLCODE THRU UT 97894 P-CHECK-SQLCODE-EXIT IF (WS 960 -R-NORMAL AND WS 400 -INDICATOR-VARIABLE >= 0) MOVE WS 400 -NUMB TO …. END-IF

#4 Checking for MULTROWs with a Singleton SELECT • If SQLCODE is OK (=0); SET WS 960 -HANDLE-NOTFND TO TRUE SET WS 960 -HANDLE-MULTROW TO TRUE EXEC SQL SELECT 1 INTO : WS 400 -NUMB FROM VCP 97160 WHERE I_CLIE = : CP 160 -I-CLIE AND I_CODE = : CP 160 -I-CODE END-EXEC PERFORM UT 97894 P-CHECK-SQLCODE THRU UT 97894 P-CHECK-SQLCODEEXIT EVALUATE TRUE WHEN WS 960 -R-NOTFND …. WHEN WS 960 -R-MULTROW …. END-EVALUATE • 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

#5 Returning a Value From Any Row SET WS 960 -HANDLE-NOTFND TO TRUE EXEC SQL SELECT C_N_STAN INTO : CP 270 -C-N-STAN WHERE I_CLIE = : CP 270 -I-CLIE AND I_N_STAN = : CP 270 -I-N-STAN AND I_N_STAN_ASSC = : CP 270 -I-N-STAN-ASSC FROM VRS 97100 FETCH FIRST ROW ONLY END-EXEC PERFORM UT 97894 P-CHECK-SQLCODE THRU UT 97894 P-CHECK-SQLCODE-EXIT IF WS 960 -R-NORMAL …. END-IF – 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!

DB 2 Unavailable Resources SQLState: 57011

Common Resource Type Codes Type Object 100 Database 200 & 202 Tablespace Type Object 302 Tablespace Page 303 Indexspace Page 201 Indexspace 500 Storage Group 210 Partition 600 EDM Pool 220 Dataset 700 Bufferpool 230 Temporary File 800 Plan 240 Procedure 801 Package 300 Page 901 Sort Storage

DB 2 Deadlocks & Timeouts SQLState: 40001

Other Technical Resources • Area Experts • DB 2 Messages • DB 2 Codes • DB 2 Web Site www. ibm. com

Questions