COBOL Code Optimization

To assist in the optimization of the code, you should use the OPTIMIZE compiler option. With the
OPTIMIZE(STD) or OPTIMIZE(FULL) options in effect, you may receive optimizations that include:

• eliminating unnecessary branches
• simplifying inefficient branches
• simplifying the code for the out-of-line PERFORM statement, moving the performed paragraphs in-line,where possible
• simplifying the code for a CALL to a contained (nested) program, moving the called statements in-line,where possible
• eliminating duplicate computations
• eliminating constant computations
• aggregating moves of contiguous, equal-sized items into a single move
• deleting unreachable code

Additionally, with the OPTIMIZE(FULL) option in effect, you may also receive these optimizations:

• deleting unreferenced data items and the associated code to initialize their VALUE clauses

Many of these optimizations are not available with OS/VS COBOL, but are available with IBM Enterprise
COBOL. NOOPTIMIZE is generally used while a program is being developed when frequent compiles are necessary. NOOPTIMIZE can make it easier to debug a program since code is not moved. However, IBM Enterprise COBOL now supports debugging programs compiled with the OPTIMIZE compiler option.
OPTIMIZE requires more CPU time for compiles than NOOPTIMIZE, but generally produces more
efficient run-time code. For production runs, OPTIMIZE is recommended.

WARNING: If your program relies upon unreferenced level 01 or level 77 data items, you should not use
OPTIMIZE(FULL), since OPTIMIZE(FULL) will delete all unreferenced data items. One way to prevent
the data item from being deleted by the OPTIMIZE(FULL) option is to refer to the data item in the
PROCEDURE DIVISION (for example, initialize the data item with a PROCEDURE DIVISION
statement instead of with VALUE clauses).

Performance considerations using OPTIMIZE:

• On the average, OPTIMIZE(STD) was 1% faster than NOOPTIMIZE, with a range of 7% faster to equivalent.
• On the average, OPTIMIZE(FULL) was equivalent to OPTIMIZE(STD).

ARITH Compiler Option

ARITH affects the maximum number of digits that you can code for integers, and the number of digits used in fixed-point intermediate results. By specifying ARITH complier option we can control the maximum number of digits allowed for decimal numbers (packed decimal, zoned decimal and numeric-edited data items and numeric literals).
 

ARITH(EXTEND)  - Max. Number of digits 31.
ARITH(COMPAT) - Max. Number of digits 18. 

Default is: ARITH(COMPAT)

Abbreviations are: AR(C|E)

Arith-extend is performance hampering because of larger intermediate results.

When you specify ARITH(EXTEND):

• The maximum number of digit positions that you can specify in the PICTURE clause for packed-decimal, external-decimal, and numeric-edited data items is raised from 18 to 31.
• The maximum number of digits that you can specify in a fixed-point numeric literal is raised from 18 to 31. You can use numeric literals with large precision anywhere that numeric literals are currently allowed, including:
  • Operands of PROCEDURE DIVISION statements
  • VALUE clauses (for numeric data items with large-precision PICTURE)
  • Condition-name values (on numeric data items with large-precision PICTURE)
• The maximum number of digits that you can specify in the arguments to NUMVAL and NUMVAL-C is raised from 18 to 31.
• The maximum value of the integer argument to the FACTORIAL function is 29.
• Intermediate results in arithmetic statements use extended mode.

When you specify ARITH(COMPAT):

• The maximum number of digit positions in the PICTURE clause for packed-decimal, external-decimal, and numeric-edited data items is 18.
• The maximum number of digits in a fixed-point numeric literal is 18.
• The maximum number of digits in the arguments to NUMVAL and NUMVAL-C is 18.
• The maximum value of the integer argument to the FACTORIAL function is 28.
• Intermediate results in arithmetic statements use compatibility mode.

Converting a file from VB to FB.

DFSORT can be used to do VB to FB conversion, when sorting, copying or merging. The VTOF or CONVERT and OUTREC operands of OUTFIL can be used to change variable-length (e.g. VB) input records to fixed-length (e.g. FB) output records. VTOF or CONVERT indicates that conversion is to be performed and OUTREC defines the reformatted records. All output data sets for which VTOF or CONVERT is used must have or will be given fixed-length record formats.

An example of OUTFIL conversion:

//VTOF EXEC PGM=SORT
//SYSPRINT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SYSDUMP DD SYSOUT=*
//SORTIN DD DSN=M145.TEMP.SORT.IN,DISP=SHR
//SORTOUT DD DSN=M145.TEMP.SORT.OUT,
// DISP=(NEW,CATLG,DELETE),UNIT=DASD,
// SPACE=(TRK,(500,500),RLSE),
// DCB=(RECFM=FB,LRECL=80)
//SYSIN DD *
            SORT FIELDS=COPY
            OUTFIL FNAMES=SORTOUT,VTOF,OUTREC=(5,80),VLFILL=C'*'
//*

The above step will copy the records from SORTIN to SORTOUT. If the input records are shorter than 80 bytes, the output is padded with ‘*’ (Specified by VLFILL).

If VLFILL option is not specified, by default the records are padded with spaces.

VLFILL=C’x’ => pad with the character x.
VLFILL=X’yy’ => pad with hexadecimal character X’yy’.

Recovering Deleted Flat Files

Flat files can be recovered by following the below mentioned steps.

1. Start 6 
2. HLIST BCDS DSN('filename') 
3. Wait for the system notification
4. Type the command HRECOVER 'filename' and wait for system notification

Different variations of HRECOVER are given below.

• HRECOVER 'filename' recovers with same name
• HRECOVER ‘old filename’ NEW ('new filename') recovers and assigns new name
• HRECOVER 'file name' REPLACE recovers replacing a file with same name

The recovery might take some time. You should have HSM volume dump(s) of the volume(s) where the dataset resided for HRECOVER to work successfully. Else you will recive a message "BACKUP/DUMP COPY DOES NOT EXIST".

AUTOTYPE Suggestion

How wonderful it feels when Internet explorer suggests names for URLs when we start typing the first few letters!

Our mainframe also has the ability to do pretty much the same. Using ‘KEYS’ command on the command line, assign a free PF key with the value ‘AUTOTYPE’. We are all set.

Now use 3.4 to go to the Data Set list utility and then type the first few qualifier of the required data set. Press the desired PF key. It will show you the dataset name which is nearest match.

Options on DECLARE CURSOR

∙ Use FOR READ/FETCH ONLY or WITH UR for retrieval only cursors.
∙ Use OPTIMIZE when you know the accurate number of rows that will be fetched
∙ Use ORDER BY only when sequence is important
∙ Use WITH HOLD statement to prevent COMMIT from destroying the cursor position in batch Programs.
∙ Select only those fields that you truly need
∙ Use only DCLGEN variables as predicates

While declaring CURSOR in handler we should use OPTIMIZE FOR n ROWS, if we want pass only n rows from DB2 handler back to calling program. In this case DB2 handler only fetches n rows into the intermediate result table. The syntax is

DECLARE C1 CURSOR FOR
SELECT * FROM PACS_TRANS_TRACK
OPTIMIZE FOR 5000 ROWS
FOR FETCH ONLY

OPTIMIZE FOR tells DB2 to proceed under the assumption that at most a total of integer rows are to be retrieved from the result table. Without this clause, DB2 would assume that all rows of the result table are to be retrieved, and would optimize accordingly. Optimizing for integer rows, if at most this number of rows are fetched, could improve performance.

Common SQL Codes

Successful Execution SQLCODE
000	SUCCESSFUL EXECUTION
Warning SQLCODE
+012	THE UNQUALIFIED COLUMN NAME column-name WAS INTERPRETED AS A CORRELATED REFERENCE
+098	A DYNAMIC SQL STATEMENT ENDS WITH A SEMICOLON
+100	ROW NOT FOUND FOR FETCH, UPDATE OR DELETE, OR THE RESULT OF A QUERY IS AN EMPTY TABLE
+110	SQL UPDATE TO A DATA CAPTURE TABLE NOT SIGNALED TO ORIGINATING SUBSYSTEM
+111	THE SUBPAGES OPTION IS NOT SUPPORTED FOR TYPE 2 INDEXES
+117	THE NUMBER OF INSERT VALUES IS NOT THE SAME AS THE NUMBER OF OBJECT COLUMNS
+162	TABLESPACE database-name.tablespace-name HAS BEEN PLACED IN CHECK PENDING
+203	THE QUALIFIED COLUMN NAME column-name WAS RESOLVED USING A NON-UNIQUE OR UNEXPOSED NAME
+204	name IS AN UNDEFINED NAME
+206	column-name IS NOT A COLUMN OF AN INSERTED TABLE, UPDATED TABLE, OR ANY TABLE IDENTIFIED IN A FROM CLAUSE
+218	THE SQL STATEMENT REFERENCING A REMOTE OBJECT CANNOT BE EXPLAINED
+219	THE REQUIRED EXPLANATION TABLE table-name DOES NOT EXIST
+220	THE COLUMN column-name IN EXPLANATION TABLE table-name IS NOT DEFINED PROPERLY
+304	A VALUE WITH DATA TYPE data-type1 CANNOT BE ASSIGNED TO A HOST VARIABLE BECAUSE THE VALUE IS NOT WITHIN THE RANGE OF THE HOST VARIABLE IN POSITION position-number WITH DATA TYPE data-type2
+331	THE NULL VALUE HAS BEEN ASSIGNED TO A HOST VARIABLE BECAUSE THE STRING CANNOT BE TRANSLATED. REASON reason-code, CHARACTER code-point, HOST VARIABLE position-number
+339	THE SQL STATEMENT HAS BEEN SUCCESSFULLY EXECUTED, BUT THERE MAY BE SOME CHARACTER CONVERSION INCONSISTENCIES
+402	LOCATION location IS UNKNOWN
+403	THE LOCAL OBJECT REFERENCED BY THE CREATE ALIAS STATEMENT DOES NOT EXIST
+535	THE RESULT OF THE POSITIONED UPDATE OR DELETE MAY DEPEND ON THE ORDER OF THE ROWS.
+541	THE REFERENTIAL OR UNIQUE CONSTRAINT name HAS BEEN IGNORED BECAUSE IT IS A DUPLICATE
+551	auth-id DOES NOT HAVE THE PRIVILEGE TO PERFORM OPERATION operation ON OBJECT object-name
+552	auth-id DOES NOT HAVE THE PRIVILEGE TO PERFORM OPERATION operation
+558	THE WITH GRANT OPTION IS IGNORED
+561	THE ALTER, INDEX, AND REFERENCES PRIVILEGES CANNOT BE GRANTED TO PUBLIC AT ALL LOCATIONS
+562	A GRANT OF A PRIVILEGE WAS IGNORED BECAUSE THE GRANTEE ALREADY HAS THE PRIVILEGE FROM THE GRANTOR
+610	INDEX index-name HAS BEEN PLACED IN RECOVER PENDING
+625	THE DEFINITION OF TABLE table-name HAS BEEN CHANGED TO INCOMPLETE
+626	DROPPING THE INDEX TERMINATES ENFORCEMENT OF THE UNIQUENESS OF A KEY THAT WAS DEFINED WHEN THE TABLE WAS CREATED
+645	WHERE NOT NULL IS IGNORED BECAUSE THE INDEX KEY CANNOT CONTAIN NULL VALUES
+650	THE TABLE BEING CREATED OR ALTERED CANNOT BECOME A DEPENDENT TABLE
+653	TABLE table-name IN PARTITIONED TABLESPACE tspace-name IS NOT AVAILABLE BECAUSE ITS PARTITIONED INDEX HAS NOT BEEN CREATED
+658	THE SUBPAGES VALUE IS IGNORED FOR THE CATALOG INDEX index-name
+664	THE INTERNAL LENGTH OF THE LIMIT-KEY FIELDS FOR THE PARTITIONED INDEX index-name EXCEEDS THE LENGTH IMPOSED BY THE INDEX MANAGER
+738	DEFINITION CHANGE OF object object_name MAY REQUIRE SIMILAR CHANGE ON READ-ONLY SYSTEMS
+802	EXCEPTION ERROR exception-type HAS OCCURRED DURING operation-type OPERATION ON data-type DATA, POSITION position-number
+806	BIND ISOLATION LEVEL RR CONFLICTS WITH TABLESPACE LOCKSIZE PAGE OR LOCKSIZE ROW AND LOCKMAX 0
+807	THE RESULT OF DECIMAL MULTIPLICATION MAY CAUSE OVERFLOW
+863	THE CONNECTION WAS SUCCESSFUL BUT ONLY SBCS WILL BE SUPPORTED
+2000	TYPE 1 INDEXES WITH SUBPAGES GREATER THAN 1 CANNOT BECOME GROUP BUFFER POOL DEPENDENT IN A DATA SHARING ENVIRONMENT
+30100	OPERATION COMPLETED SUCCESSFULLY BUT A DISTRIBUTION PROTOCOL VIOLATION HAS BEEN DETECTED. ORIGINAL SQLCODE=original-sqlcode AND ORIGINAL SQLSTATE=original-sqlstate
Error SQLCODE
-007	STATEMENT CONTAINS THE ILLEGAL CHARACTER character
-010	THE STRING CONSTANT BEGINNING string IS NOT TERMINATED
-029	INTO CLAUSE REQUIRED
-060	INVALID type SPECIFICATION : spec
-084	UNACCEPTABLE SQL STATEMENT
-101	THE STATEMENT IS TOO LONG OR TOO COMPLEX
-102	LITERAL STRING IS TOO LONG. STRING BEGINS string
-103	literal IS AN INVALID NUMERIC LITERAL
-104	ILLEGAL SYMBOL "token". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: token-list
-105	INVALID STRING
-107	THE NAME name IS TOO LONG. MAXIMUM ALLOWABLE SIZE IS size
-109	clause CLAUSE IS NOT PERMITTED
-110	INVALID HEXADECIMAL LITERAL BEGINNING string
-111	A COLUMN FUNCTION DOES NOT INCLUDE A COLUMN NAME
-112	THE OPERAND OF A COLUMN FUNCTION IS ANOTHER COLUMN FUNCTION
-113	INVALID CHARACTER FOUND IN string, REASON CODE - nnn
-114	THE LOCATION NAME location DOES NOT MATCH THE CURRENT SERVER
-115	A PREDICATE IS INVALID BECAUSE THE COMPARISON OPERATOR operator IS FOLLOWED BY A PARENTHESIZED LIST OR BY ANY OR ALL WITHOUT A SUBQUERY
-117	THE NUMBER OF INSERT VALUES IS NOT THE SAME AS THE NUMBER OF OBJECT COLUMNS
-118	THE OBJECT TABLE OR VIEW OF THE INSERT, DELETE, OR UPDATE STATEMENT IS ALSO IDENTIFIED IN A FROM CLAUSE
-119	A COLUMN IDENTIFIED IN A HAVING CLAUSE IS NOT INCLUDED IN THE GROUP BY CLAUSE
-120	A WHERE CLAUSE OR SET CLAUSE INCLUDES A COLUMN FUNCTION
-121	THE COLUMN name IS IDENTIFIED MORE THAN ONCE IN THE INSERT OR UPDATE STATEMENT
-122	A SELECT STATEMENT WITH NO GROUP BY CLAUSE CONTAINS A COLUMN NAME AND A COLUMN FUNCTION IN THE SELECT CLAUSE OR A COLUMN NAME IS CONTAINED IN THE SELECT CLAUSE BUT NOT IN THE GROUP BY CLAUSE
-125	AN INTEGER IN THE ORDER BY CLAUSE DOES NOT IDENTIFY A COLUMN OF THE RESULT
-126	THE SELECT STATEMENT CONTAINS BOTH AN UPDATE CLAUSE AND AN ORDER BY CLAUSE
-127	DISTINCT IS SPECIFIED MORE THAN ONCE IN A SUBSELECT
-128	INVALID USE OF NULL IN A PREDICATE
-129	THE STATEMENT CONTAINS TOO MANY TABLE NAMES
-130	THE ESCAPE CLAUSE CONSISTS OF MORE THAN ONE CHARACTER, OR THE STRING PATTERN CONTAINS AN INVALID OCCURRENCE OF THE ESCAPE CHARACTER
-131	STATEMENT WITH LIKE PREDICATE HAS INCOMPATIBLE DATA TYPES
-132	A LIKE PREDICATE IS INVALID BECAUSE THE SECOND OPERAND IS NOT A STRING
-133	A COLUMN FUNCTION IN A SUBQUERY OF A HAVING CLAUSE IS INVALID BECAUSE ALL COLUMN REFERENCES IN ITS ARGUMENT ARE NOT CORRELATED TO THE GROUP BY RESULT THAT THE HAVING CLAUSE IS APPLIED TO
-134	IMPROPER USE OF LONG STRING COLUMN column-name OR A HOST VARIABLE OF MAXIMUM LENGTH GREATER THAN 254
-136	SORT CANNOT BE EXECUTED BECAUSE THE SORT KEY LENGTH IS GREATER THAN 4000 BYTES
-137	RESULT OF CONCATENATION TOO LONG
-138	THE SECOND OR THIRD ARGUMENT OF THE SUBSTR FUNCTION IS OUT OF RANGE
-144	INVALID SECTION NUMBER number
-150	THE OBJECT OF THE INSERT, DELETE, OR UPDATE STATEMENT IS A VIEW FOR WHICH THE REQUESTED OPERATION IS NOT PERMITTED
-151	THE UPDATE STATEMENT IS INVALID BECAUSE THE CATALOG DESCRIPTION OF COLUMN column-name INDICATES THAT IT CANNOT BE UPDATED
-152	THE DROP clause CLAUSE IN THE ALTER STATEMENT IS INVALID BECAUSE constraint-name IS A constraint-type
-153	THE CREATE VIEW STATEMENT DOES NOT INCLUDE A REQUIRED COLUMN LIST
-154	THE CREATE VIEW FAILED BECAUSE THE VIEW DEFINITION CONTAINS A UNION, A UNION ALL, OR A REMOTE OBJECT
-156	THE STATEMENT DOES NOT IDENTIFY A TABLE
-157	ONLY A TABLE NAME CAN BE SPECIFIED IN A FOREIGN KEY CLAUSE. object-name IS NOT THE NAME OF A TABLE.
-158	THE NUMBER OF COLUMNS SPECIFIED FOR THE VIEW IS NOT THE SAME AS THE NUMBER OF COLUMNS SPECIFIED BY THE SELECT CLAUSE
-159	DROP OR COMMENT ON token IDENTIFIES A(N) token RATHER THAN A(N) token
-160	THE WITH CHECK OPTION CANNOT BE USED FOR THE SPECIFIED VIEW
-161	THE INSERT OR UPDATE IS NOT ALLOWED BECAUSE A RESULTING ROW DOES NOT SATISFY THE VIEW DEFINITION
-164	auth-id1 DOES NOT HAVE THE PRIVILEGE TO CREATE A VIEW WITH QUALIFICATION authorization ID
-170	THE NUMBER OF ARGUMENTS SPECIFIED FOR function-name IS INVALID
-171	THE DATA TYPE, LENGTH, OR VALUE OF ARGUMENT nn OF function-name IS INVALID
-173	UR IS SPECIFIED ON THE WITH CLAUSE BUT THE CURSOR IS NOT READ-ONLY
-180	THE STRING REPRESENTATION OF A DATETIME VALUE HAS INVALID SYNTAX
-181	THE STRING REPRESENTATION OF A DATETIME VALUE IS NOT A VALID DATETIME VALUE
-182	AN ARITHMETIC EXPRESSION WITH A DATETIME VALUE IS INVALID
-183	AN ARITHMETIC OPERATION ON A DATE OR TIMESTAMP HAS A RESULT THAT IS NOT WITHIN THE VALID RANGE OF DATES
-184	AN ARITHMETIC EXPRESSION WITH A DATETIME VALUE CONTAINS A PARAMETER MARKER
-185	THE LOCAL FORMAT OPTION HAS BEEN USED WITH A DATE OR TIME AND NO LOCAL EXIT HAS BEEN INSTALLED
-186	THE LOCAL DATE LENGTH OR LOCAL TIME LENGTH HAS BEEN INCREASED AND EXECUTING PROGRAM RELIES ON THE OLD LENGTH
-187	A REFERENCE TO A CURRENT DATE/TIME SPECIAL REGISTER IS INVALID BECAUSE THE MVS TOD CLOCK IS BAD OR THE MVS PARMTZ IS OUT OF RANGE
-188	THE STRING REPRESENTATION OF A NAME IS INVALID
-189	CCSID ccsid IS UNKNOWN OR INVALID FOR THE DATA TYPE OR SUBTYPE
-191	A STRING CANNOT BE USED BECAUSE IT IS INVALID MIXED DATA
-197	QUALIFIED COLUMN NAMES IN ORDER BY CLAUSE NOT PERMITTED WHEN UNION OR UNION ALL SPECIFIED
-198	THE OPERAND OF THE PREPARE OR EXECUTE IMMEDIATE STATEMENT IS BLANK OR EMPTY
-199	ILLEGAL USE OF KEYWORD keyword. TOKEN token-list WAS EXPECTED
-203	A REFERENCE TO COLUMN column-name IS AMBIGUOUS
-204	name IS AN UNDEFINED NAME
-205	column-name IS NOT A COLUMN OF TABLE table-name
-206	column-name IS NOT A COLUMN OF AN INSERTED TABLE, UPDATED TABLE, OR ANY TABLE IDENTIFIED IN A FROM CLAUSE
-208	THE ORDER BY CLAUSE IS INVALID BECAUSE COLUMN name IS NOT PART OF THE RESULT TABLE
-198	THE OPERAND OF THE PREPARE OR EXECUTE IMMEDIATE STATEMENT IS BLANK OR EMPTY
-199	ILLEGAL USE OF KEYWORD keyword. TOKEN token-list WAS EXPECTED
-203	A REFERENCE TO COLUMN column-name IS AMBIGUOUS
-204	name IS AN UNDEFINED NAME
-205	column-name IS NOT A COLUMN OF TABLE table-name
-206	column-name IS NOT A COLUMN OF AN INSERTED TABLE, UPDATED TABLE, OR ANY TABLE IDENTIFIED IN A FROM CLAUSE
-208	THE ORDER BY CLAUSE IS INVALID BECAUSE COLUMN name IS NOT PART OF THE RESULT TABLE
-219	THE REQUIRED EXPLANATION TABLE table-name DOES NOT EXIST
-220	THE COLUMN column-name IN EXPLANATION TABLE table-name IS NOT DEFINED PROPERLY
-221	"SET OF OPTIONAL COLUMNS" IN EXPLANATION TABLE table-name IS INCOMPLETE. OPTIONAL COLUMN column-name IS MISSING
-250	THE LOCAL LOCATION NAME IS NOT DEFINED WHEN PROCESSING A THREE-PART OBJECT NAME
-251	TOKEN name IS NOT VALID
-300	THE STRING CONTAINED IN HOST VARIABLE OR PARAMETER position-number IS NOT NUL-TERMINATED
-301	THE VALUE OF INPUT HOST VARIABLE OR PARAMETER NUMBER position-number CANNOT BE USED AS SPECIFIED BECAUSE OF ITS DATA TYPE
-302	THE VALUE OF INPUT VARIABLE OR PARAMETER NUMBER position-number IS INVALID OR TOO LARGE FOR THE TARGET COLUMN OR THE TARGET VALUE
-303	A VALUE CANNOT BE ASSIGNED TO OUTPUT HOST VARIABLE NUMBER position-number BECAUSE THE DATA TYPES ARE NOT COMPARABLE
-304	A VALUE WITH DATA TYPE data-type1 CANNOT BE ASSIGNED TO A HOST VARIABLE BECAUSE THE VALUE IS NOT WITHIN THE RANGE OF THE HOST VARIABLE IN POSITION position-number WITH DATA TYPE data-type2
-305	THE NULL VALUE CANNOT BE ASSIGNED TO OUTPUT HOST VARIABLE NUMBER position-number BECAUSE NO INDICATOR VARIABLE IS SPECIFIED
-309	A PREDICATE IS INVALID BECAUSE A REFERENCED HOST VARIABLE HAS THE NULL VALUE
-310	DECIMAL HOST VARIABLE OR PARAMETER number CONTAINS NON DECIMAL DATA.
-311	THE LENGTH OF INPUT HOST VARIABLE NUMBER position-number IS NEGATIVE OR GREATER THAN THE MAXIMUM
-312	UNDEFINED OR UNUSABLE HOST VARIABLE variable-name
-313	THE NUMBER OF HOST VARIABLES SPECIFIED IS NOT EQUAL TO THE NUMBER OF PARAMETER MARKERS
-314	THE STATEMENT CONTAINS AN AMBIGUOUS HOST VARIABLE REFERENCE
-330	A STRING CANNOT BE USED BECAUSE IT CANNOT BE TRANSLATED. REASON reason-code, CHARACTER code-point, HOST VARIABLE position-number
-331	A STRING CANNOT BE ASSIGNED TO A HOST VARIABLE BECAUSE IT CANNOT BE TRANSLATED. REASON reason-code, CHARACTER code-point, POSITION position-number
-332	SYSSTRINGS DOES NOT DEFINE A TRANSLATION FROM CCSID ccsid TO ccsid
-333	THE SUBTYPE OF A STRING VARIABLE IS NOT THE SAME AS THE SUBTYPE KNOWN AT BIND TIME AND THE DIFFERENCE CANNOT BE RESOLVED BY TRANSLATION
-338	AN ON CLAUSE IS INVALID
-339	THE SQL STATEMENT CANNOT BE EXECUTED FROM AN ASCII BASED DRDA APPLICATION REQUESTOR TO A V2R2 DB2 SUBSYSTEM
-351	AN UNSUPPORTED SQLTYPE WAS ENCOUNTERED IN POSITION "" ON A PREPARE or DESCRIBE OPERATION
-400	THE CATALOG HAS THE MAXIMUM NUMBER OF USER DEFINED INDEXES
-401	THE OPERANDS OF AN ARITHMETIC OR COMPARISON OPERATION ARE NOT COMPARABLE
-402	AN ARITHMETIC FUNCTION OR OPERATOR arith-fop IS APPLIED TO CHARACTER OR DATETIME DATA
-404	THE UPDATE OR INSERT STATEMENT SPECIFIES A STRING THAT IS TOO LONG column-name
-405	THE NUMERIC LITERAL literal CANNOT BE USED AS SPECIFIED BECAUSE IT IS OUT OF RANGE
-406	A CALCULATED OR DERIVED NUMERIC VALUE IS NOT WITHIN THE RANGE OF ITS OBJECT COLUMN
-407	AN UPDATE OR INSERT VALUE IS NULL, BUT THE OBJECT COLUMN column-name CANNOT CONTAIN NULL VALUES
-408	AN UPDATE OR INSERT VALUE IS NOT COMPARABLE WITH THE DATA TYPE OF ITS OBJECT COLUMN column-name
-409	INVALID OPERAND OF A COUNT FUNCTION
-410	THE FLOATING POINT LITERAL literal CONTAINS MORE THAN 30 CHARACTERS
-411	CURRENT SQLID CANNOT BE USED IN A STATEMENT THAT REFERENCES REMOTE OBJECTS
-412	THE SELECT CLAUSE OF A SUBQUERY SPECIFIES MULTIPLE COLUMNS
-414	A LIKE PREDICATE IS INVALID BECAUSE THE FIRST OPERAND IS NOT A STRING
-415	THE CORRESPONDING COLUMNS, column-number, OF THE OPERANDS OF A UNION OR A UNION ALL DO NOT HAVE COMPARABLE COLUMN DESCRIPTIONS
-416	AN OPERAND OF A UNION CONTAINS A LONG STRING COLUMN
-417	A STATEMENT STRING TO BE PREPARED INCLUDES PARAMETER MARKERS AS THE OPERANDS OF THE SAME OPERATOR
-418	A STATEMENT STRING TO BE PREPARED CONTAINS AN INVALID USE OF PARAMETER MARKERS
-419	THE DECIMAL DIVIDE OPERATION IS INVALID BECAUSE THE RESULT WOULD HAVE A NEGATIVE SCALE
-420	THE VALUE OF A CHARACTER STRING ARGUMENT WAS NOT ACCEPTABLE TO THE function-name FUNCTION
-421	THE OPERANDS OF A UNION OR UNION ALL DO NOT HAVE THE SAME NUMBER OF COLUMNS
-426	DYNAMIC COMMIT NOT VALID AT AN APPLICATION SERVER WHERE UPDATES ARE NOT ALLOWED
-427	DYNAMIC ROLLBACK NOT VALID AT AN APPLICATION SERVER WHERE UPDATES ARE NOT ALLOWED
-440	THE NUMBER OF PARAMETERS IN THE PARAMETER LIST DOES NOT MATCH THE NUMBER OF PARAMETERS EXPECTED FOR STORED PROCEDURE name, AUTHID authid, LUNAME luname. number PARAMETERS WERE EXPECTED.
-444	USER PROGRAM name COULD NOT BE FOUND
-450	STORED PROCEDURE name, PARAMETER NUMBER number, OVERLAYED STORAGE BEYOND ITS DECLARED LENGTH
-469	SQL CALL STATEMENT MUST SPECIFY AN OUTPUT HOST VARIABLE FOR PARAMETER number.
-470	SQL CALL STATEMENT SPECIFIED A NULL VALUE FOR INPUT PARAMETER number, BUT THE STORED PROCEDURE DOES NOT SUPPORT NULL VALUES
-471	SQL CALL FOR STORED PROCEDURE name FAILED DUE TO REASON rc
-500	THE IDENTIFIED CURSOR WAS CLOSED WHEN THE CONNECTION WAS DESTROYED
-501	THE CURSOR IDENTIFIED IN A FETCH OR CLOSE STATEMENT IS NOT OPEN
-502	THE CURSOR IDENTIFIED IN AN OPEN STATEMENT IS ALREADY OPEN
-503	A COLUMN CANNOT BE UPDATED BECAUSE IT IS NOT IDENTIFIED IN THE UPDATE CLAUSE OF THE SELECT STATEMENT OF THE CURSOR
-504	THE CURSOR NAME cursor-name IS NOT DEFINED
-507	THE CURSOR IDENTIFIED IN THE UPDATE OR DELETE STATEMENT IS NOT OPEN
-508	THE CURSOR IDENTIFIED IN THE UPDATE OR DELETE STATEMENT IS NOT POSITIONED ON A ROW
-509	THE TABLE IDENTIFIED IN THE UPDATE OR DELETE STATEMENT IS NOT THE SAME TABLE DESIGNATED BY THE CURSOR
-510	THE TABLE DESIGNATED BY THE CURSOR OF THE UPDATE OR DELETE STATEMENT CANNOT BE MODIFIED
-511	THE FOR UPDATE CLAUSE CANNOT BE SPECIFIED BECAUSE THE TABLE DESIGNATED BY THE CURSOR CANNOT BE MODIFIED
-512	STATEMENT REFERENCE TO REMOTE OBJECT IS INVALID
-513	THE ALIAS alias-name MUST NOT BE DEFINED ON ANOTHER LOCAL OR REMOTE ALIAS
-514	THE CURSOR cursor-name IS NOT IN A PREPARED STATE
-516	THE DESCRIBE STATEMENT DOES NOT IDENTIFY A PREPARED STATEMENT
-517	CURSOR cursor-name CANNOT BE USED BECAUSE ITS STATEMENT NAME DOES NOT IDENTIFY A PREPARED SELECT STATEMENT
-518	THE EXECUTE STATEMENT DOES NOT IDENTIFY A VALID PREPARED STATEMENT
-519	THE PREPARE STATEMENT IDENTIFIES THE SELECT STATEMENT OF THE OPENED CURSOR cursor-name
-525	THE SQL STATEMENT CANNOT BE EXECUTED BECAUSE IT WAS IN ERROR AT BIND TIME FOR SECTION = sectno PACKAGE = pkgname CONSISTENCY TOKEN = X'contoken'
-530	THE INSERT OR UPDATE VALUE OF FOREIGN KEY constraint-name IS INVALID
-531	THE PRIMARY KEY IN A PARENT ROW CANNOT BE UPDATED BECAUSE IT HAS ONE OR MORE DEPENDENT ROWS IN RELATIONSHIP constraint-name
-532	THE RELATIONSHIP constraint-name RESTRICTS THE DELETION OF ROW WITH RID X'rid-number'
-533	INVALID MULTIPLE-ROW INSERT
-534	THE PRIMARY KEY CANNOT BE UPDATED BECAUSE OF MULTIPLE-ROW UPDATE
-536	THE DELETE STATEMENT IS INVALID BECAUSE TABLE table-name CAN BE AFFECTED BY THE OPERATION
-537	THE PRIMARY KEY CLAUSE, A FOREIGN KEY CLAUSE, OR A UNIQUE CLAUSE IDENTIFIES COLUMN column-name MORE THAN ONCE
-538	FOREIGN KEY name DOES NOT CONFORM TO THE DESCRIPTION OF THE PRIMARY KEY OF TABLE table-name
-539	TABLE table-name DOES NOT HAVE A PRIMARY KEY
-540	THE DEFINITION OF TABLE table-name IS INCOMPLETE BECAUSE IT LACKS A PRIMARY INDEX OR A REQUIRED UNIQUE INDEX
-542	column-name CANNOT BE A COLUMN OF A PRIMARY KEY OR A UNIQUE CONSTRAINT BECAUSE IT CAN CONTAIN NULL VALUES
-543	A ROW IN A PARENT TABLE CANNOT BE DELETED BECAUSE THE CHECK CONSTRAINT check-constraint RESTRICTS THE DELETION
-544	THE CHECK CONSTRAINT SPECIFIED IN THE ALTER TABLE STATEMENT CANNOT BE ADDED BECAUSE AN EXISTING ROW VIOLATES THE CHECK CONSTRAINT
-545	THE REQUESTED OPERATION IS NOT ALLOWED BECAUSE A ROW DOES NOT SATISFY THE CHECK CONSTRAINT check-constraint
-546	THE CHECK CONSTRAINT constraint-name IS INVALID
-548	A CHECK CONSTRAINT THAT IS DEFINED WITH column-name IS INVALID
-549	THE statement STATEMENT IS NOT ALLOWED FOR object_type1 object_name BECAUSE THE BIND OPTION DYNAMICRULES(BIND) IN THE object_type2 IS IN EFFECT
-551	auth-id DOES NOT HAVE THE PRIVILEGE TO PERFORM OPERATION operation ON OBJECT object-name
-552	auth-id DOES NOT HAVE THE PRIVILEGE TO PERFORM OPERATION operation
-553	auth-id SPECIFIED IS NOT ONE OF THE VALID AUTHORIZATION IDS
-554	AN AUTHORIZATION ID CANNOT GRANT A PRIVILEGE TO ITSELF
-555	AN AUTHORIZATION ID CANNOT REVOKE A PRIVILEGE FROM ITSELF
-556	authid2 CANNOT HAVE THE privilege PRIVILEGE on_object REVOKED BY authid1 BECAUSE THE REVOKEE DOES NOT POSSESS THE PRIVILEGE OR THE REVOKER DID NOT MAKE THE GRANT
-557	INCONSISTENT GRANT/REVOKE KEYWORD keyword. PERMITTED KEYWORDS ARE keyword-list
-558	INVALID CLAUSE OR COMBINATION OF CLAUSES ON A GRANT OR REVOKE
-559	ALL AUTHORIZATION FUNCTIONS HAVE BEEN DISABLED
-567	bind-type AUTHORIZATION ERROR USING auth-id AUTHORITY PACKAGE = package-name PRIVILEGE = privilege
-571	THE STATEMENT WOULD RESULT IN A MULTIPLE SITE UPDATE
-574	THE SPECIFIED DEFAULT VALUE CONFLICTS WITH THE COLUMN DEFINITION.
-601	THE NAME OF THE OBJECT TO BE CREATED IS IDENTICAL TO THE EXISTING NAME name OF THE OBJECT TYPE obj-type
-602	TOO MANY COLUMNS SPECIFIED IN A CREATE INDEX
-603	A UNIQUE INDEX CANNOT BE CREATED BECAUSE THE TABLE CONTAINS ROWS WHICH ARE DUPLICATES WITH RESPECT TO THE VALUES OF THE IDENTIFIED COLUMNS
-604	A COLUMN DEFINITION SPECIFIES AN INVALID LENGTH, PRECISION, OR SCALE ATTRIBUTE
-607	OPERATION OR OPTION operation IS NOT DEFINED FOR THIS OBJECT
-611	ONLY LOCKMAX 0 CAN BE SPECIFIED WHEN THE LOCK SIZE OF THE TABLESPACE IS TABLESPACE OR TABLE
-612	column-name IS A DUPLICATE COLUMN NAME
-613	THE PRIMARY KEY OR A UNIQUE CONSTRAINT IS TOO LONG OR HAS TOO MANY COLUMNS
-614	THE INDEX CANNOT BE CREATED BECAUSE THE SUM OF THE INTERNAL LENGTHS OF THE IDENTIFIED COLUMNS IS GREATER THAN THE ALLOWABLE MAXIMUM
-615	operation-type IS NOT ALLOWED ON A PACKAGE IN USE
-616	obj-type1 obj-name1 CANNOT BE DROPPED BECAUSE IT IS REFERENCED BY obj-type2 obj-name2
-617	A TYPE 1 INDEX CANNOT BE DEFINED ON A TABLE IN A TABLE SPACE WITH LOCKSIZE ROW
-618	OPERATION operation IS NOT ALLOWED ON SYSTEM DATABASES
-619	OPERATION DISALLOWED BECAUSE THE WORK FILE DATABASE IS NOT STOPPED
-620	KEYWORD keyword IN stmt type STATEMENT IS NOT PERMITTED FOR A TABLE SPACE IN THE WORK FILE DATABASE
-621	DUPLICATE DBID dbid WAS DETECTED AND PREVIOUSLY ASSIGNED TO database-name
-622	FOR MIXED DATA IS INVALID BECAUSE THE MIXED DATA INSTALL OPTION IS NO
-623	A CLUSTERING INDEX ALREADY EXISTS ON TABLE table-name
-624	TABLE table-name ALREADY HAS A PRIMARY KEY
-625	TABLE table-name DOES NOT HAVE AN INDEX TO ENFORCE THE UNIQUENESS OF THE PRIMARY KEY
-626	THE ALTER STATEMENT IS NOT EXECUTABLE BECAUSE THE PAGE SET IS NOT STOPPED
-627	THE ALTER STATEMENT IS INVALID BECAUSE THE PAGESET HAS USER-MANAGED DATA SETS
-628	THE CLAUSES ARE MUTUALLY EXCLUSIVE.
-629	SET NULL CANNOT BE SPECIFIED BECAUSE FOREIGN KEY name CANNOT CONTAIN NULL VALUES
-630	THE WHERE NOT NULL SPECIFICATION IS INVALID FOR TYPE 1 INDEXES
-631	FOREIGN KEY name IS TOO LONG OR HAS TOO MANY COLUMNS
-632	THE TABLE CANNOT BE DEFINED AS A DEPENDENT OF table-name BECAUSE OF DELETE RULE RESTRICTIONS
-633	THE DELETE RULE MUST BE delete-rule
-634	THE DELETE RULE MUST NOT BE CASCADE
-635	THE DELETE RULES CANNOT BE DIFFERENT OR CANNOT BE SET NULL
-636	THE PARTITIONING KEYS ARE NOT SPECIFIED IN ASCENDING OR DESCENDING ORDER
-637	DUPLICATE keyword KEYWORD
-638	TABLE table-name CANNOT BE CREATED BECAUSE COLUMN DEFINITION IS MISSING
-639	A NULLABLE COLUMN OF A FOREIGN KEY WITH A DELETE RULE OF SET NULL CANNOT BE A COLUMN OF THE KEY OF A PARTITIONED INDEX
-640	LOCKSIZE ROW CANNOT BE SPECIFIED BECAUSE TABLE IN THIS TABLESPACE HAS TYPE 1 INDEX
-642	TOO MANY COLUMNS IN UNIQUE CONSTRAINTS
-643	CHECK CONSTRAINT EXCEEDS MAXIMUM ALLOWABLE LENGTH
-644	INVALID VALUE SPECIFIED FOR KEYWORD keyword IN stmt-type TATEMENT
-646	TABLE table-name CANNOT BE CREATED IN PARTITIONED/DEFAULT TABLE SPACE tspace-name BECAUSE IT ALREADY CONTAINS A TABLE
-647	BUFFERPOOL bp-name CANNOT BE SPECIFIED BECAUSE IT HAS NOT BEEN ACTIVATED
-650	THE ALTER INDEX CANNOT BE EXECUTED, REASON reason
-651	TABLE DESCRIPTION EXCEEDS MAXIMUM SIZE OF OBJECT DESCRIPTOR.
-652	VIOLATION OF INSTALLATION DEFINED EDIT OR VALIDATION PROCEDURE proc-name
-653	TABLE table-name IN PARTITIONED TABLE SPACE tspace-name IS NOT AVAILABLE BECAUSE ITS PARTITIONED INDEX HAS NOT BEEN CREATED
-655	THE CREATE OR ALTER STOGROUP IS INVALID BECAUSE THE STORAGE GROUP WOULD HAVE BOTH SPECIFIC AND NON-SPECIFIC VOLUME IDS
-660	INDEX index-name CANNOT BE CREATED ON PARTITIONED TABLE SPACE tspace-name BECAUSE KEY LIMITS ARE NOT SPECIFIED
-661	INDEX index-name CANNOT BE CREATED ON PARTITIONED TABLE SPACE tspace-name BECAUSE THE NUMBER OF PART SPECIFICATIONS IS NOT EQUAL TO THE NUMBER OF PARTITIONS OF THE TABLE SPACE
-662	A PARTITIONED INDEX CANNOT BE CREATED ON A NON-PARTITIONED TABLE SPACE tspace-name
-663	THE NUMBER OF KEY LIMIT VALUES IS EITHER ZERO, OR GREATER THAN THE NUMBER OF COLUMNS IN THE KEY OF INDEX index-name
-665	THE PART CLAUSE OF AN ALTER STATEMENT IS OMITTED OR INVALID
-666	stmt-verb object CANNOT BE EXECUTED BECAUSE function IS IN PROGRESS
-667	THE CLUSTERING INDEX FOR A PARTITIONED TABLE SPACE CANNOT BE EXPLICITLY DROPPED
-668	THE COLUMN CANNOT BE ADDED TO THE TABLE BECAUSE THE TABLE HAS AN EDIT PROCEDURE
-669	A TABLE IN A PARTITIONED TABLE SPACE CANNOT BE EXPLICITLY DROPPED
-670	THE RECORD LENGTH OF THE TABLE EXCEEDS THE PAGE SIZE LIMIT
-671	THE BUFFERPOOL ATTRIBUTE OF THE TABLE SPACE CANNOT BE ALTERED AS SPECIFIED BECAUSE IT WOULD CHANGE THE PAGE SIZE OF THE TABLE SPACE
-672	OPERATION DROP NOT ALLOWED ON TABLE table_name
-676	A 32K PAGE BUFFERPOOL MAY NOT BE USED FOR AN INDEX
-677	INSUFFICIENT VIRTUAL STORAGE FOR BUFFERPOOL EXPANSION
-678	THE LITERAL literal SPECIFIED FOR THE INDEX LIMIT KEY MUST CONFORM TO THE DATA TYPE data-type OF THE CORRESPONDING COLUMN column-name
-679	THE OBJECT name CANNOT BE CREATED BECAUSE A DROP IS PENDING ON THE OBJECT
-680	TOO MANY COLUMNS SPECIFIED FOR A TABLE
-681	COLUMN column-name IN VIOLATION OF INSTALLATION DEFINED FIELD PROCEDURE. RT: return-code, RS: reason-code, MSG: message-token
-682	FIELD PROCEDURE procedure-name COULD NOT BE LOADED
-683	INVALID COLUMN TYPE FOR FIELDPROC, BIT DATA, SBCS DATA, OR MIXED DATA OPTION, column-name
-684	THE LENGTH OF LITERAL LIST BEGINNING string IS TOO LONG
-685	INVALID FIELD TYPE, column-name
-686	COLUMN DEFINED WITH A FIELD PROCEDURE CAN NOT COMPARE WITH ANOTHER COLUMN WITH DIFFERENT FIELD PROCEDURE
-687	FIELD TYPES INCOMPARABLE
-688	INCORRECT DATA RETURNED FROM FIELD PROCEDURE, column-name, msgno
-689	TOO MANY COLUMNS DEFINED FOR A DEPENDENT TABLE
-690	THE STATEMENT IS REJECTED BY DATA DEFINITION CONTROL SUPPORT. REASON reason-code
-691	THE REQUIRED REGISTRATION TABLE table-name DOES NOT EXIST
-692	THE REQUIRED UNIQUE INDEX index-name FOR DDL REGISTRATION TABLE table-name DOES NOT EXIST
-693	THE COLUMN column-name IN DDL REGISTRATION TABLE OR INDEX table-name (index-name) IS NOT DEFINED PROPERLY
-694	THE DDL STATEMENT CANNOT BE EXECUTED BECAUSE A DROP IS PENDING ON THE DDL REGISTRATION TABLE table-name
-713	THE REPLACEMENT VALUE FOR special-register IS INVALID
-715	PROGRAM program-name WITH MARK release-dependency-mark FAILED BECAUSE IT DEPENDS ON FUNCTIONS OF THE RELEASE FROM WHICH FALLBACK HAS OCCURRED
-716	PROGRAM program-name PRECOMPILED WITH INCORRECT LEVEL FOR THIS RELEASE.
-717	bind-type FOR object-type object-name WITH MARK release-dependency-mark FAILED BECAUSE object-type DEPENDS ON FUNCTIONS OF THE RELEASE FROM WHICH FALLBACK HAS OCCURRED.
-718	REBIND FOR PACKAGE package-name FAILED BECAUSE IBMREQD OF ibmreqd IS INVALID
-719	BIND ADD ERROR USING auth-id AUTHORITY PACKAGE package-name ALREADY EXISTS
-720	BIND ERROR, ATTEMPTING TO REPLACE PACKAGE = package_name WITH VERSION = version2 BUT THIS VERSION ALREADY EXISTS
-721	BIND ERROR FOR PACKAGE = pkg-id CONTOKEN = 'contoken'X IS NOT UNIQUE SO IT CANNOT BE CREATED
-722	bind-type ERROR USING auth-id AUTHORITY PACKAGE package-name DOES NOT EXIST
-726	BIND ERROR ATTEMPTING TO REPLACE PACKAGE = . THERE ARE ENABLE OR DISABLE ENTRIES CURRENTLY ASSOCIATED WITH THE PACKAGE
-730	THE PARENT OF A TABLE IN A READ-ONLY SHARED DATABASE MUST ALSO BE A TABLE IN A READ-ONLY SHARED DATABASE
-731	USER-DEFINED DATASET dsname MUST BE DEFINED WITH SHAREOPTIONS(1,3)
-732	THE DATABASE IS DEFINED ON THIS SUBSYSTEM WITH THE ROSHARE READ ATTRIBUTE BUT THE TABLE SPACE OR INDEX SPACE HAS NOT BEEN DEFINED ON THE OWNING SUBSYSTEM
-733	THE DESCRIPTION OF A TABLE SPACE, INDEX SPACE, OR TABLE IN A ROSHARE READ DATABASE MUST BE CONSISTENT WITH ITS DESCRIPTION IN THE OWNER SYSTEM
-734	THE ROSHARE ATTRIBUTE OF A DATABASE CANNOT BE ALTERED FROM ROSHARE READ
-735	DATABASE dbid CANNOT BE ACCESSED BECAUSE IT IS NO LONGER A SHARED DATABASE
-736	INVALID OBID obid SPECIFIED
-737	IMPLICIT TABLE SPACE NOT ALLOWED
-741	A WORK FILE DATABASE IS ALREADY DEFINED FOR MEMBER member-name
-742	DSNDB07 IS THE IMPLICIT WORK FILE DATABASE
-751	A STORED PROCEDURE HAS BEEN PLACED IN MUST_ROLLBACK STATE DUE TO SQL OPERATION name
-752	THE CONNECT STATEMENT IS INVALID BECAUSE THE PROCESS IS NOT IN THE CONNECTABLE STATE
-802	EXCEPTION ERROR 'exception-type' HAS OCCURRED DURING 'operation-type' OPERATION ON 'data-type' DATA, POSITION 'position-number'
-803	AN INSERTED OR UPDATED VALUE IS INVALID BECAUSE THE INDEX IN INDEX SPACE indexspace-name CONSTRAINS COLUMNS OF THE TABLE SO NO TWO ROWS CAN CONTAIN DUPLICATE VALUES IN THOSE COLUMNS. RID OF EXISTING ROW IS X'rid'
-804	AN ERROR WAS FOUND IN THE APPLICATION PROGRAM INPUT PARAMETERS FOR THE SQL STATEMENT. REASON reason
-805	DBRM or PACKAGE NAME location-name.collection-id.dbrm-name.consistency -token NOT FOUND IN PLAN plan-name. REASON reason
-807	ACCESS DENIED: PACKAGE package-name IS NOT ENABLED FOR ACCESS FROM connection-type connection-name
-808	THE CONNECT STATEMENT IS NOT CONSISTENT WITH THE FIRST CONNECT STATEMENT
-811	THE RESULT OF AN EMBEDDED SELECT STATEMENT IS A TABLE OF MORE THAN ONE ROW, OR THE RESULT OF THE SUBQUERY OF A BASIC PREDICATE IS MORE THAN ONE VALUE
-812	THE SQL STATEMENT CANNOT BE PROCESSED BECAUSE A BLANK COLLECTION-ID WAS FOUND IN THE CURRENT PACKAGESET SPECIAL REGISTER WHILE TRYING TO FORM A QUALIFIED PACKAGE NAME FOR PROGRAM program-name.consistency-token USING PLAN plan-name
-815	A GROUP BY OR HAVING CLAUSE IS IMPLICITLY OR EXPLICITLY SPECIFIED IN AN EMBEDDED SELECT STATEMENT OR A SUBQUERY OF A BASIC PREDICATE
-817	THE SQL STATEMENT CANNOT BE EXECUTED BECAUSE THE STATEMENT WILL RESULT IN A PROHIBITED UPDATE OPERATION
-818	THE PRECOMPILER-GENERATED TIMESTAMP x IN THE LOAD MODULE IS DIFFERENT FROM THE BIND TIMESTAMP y BUILT FROM THE DBRM z
-819	THE VIEW CANNOT BE PROCESSED BECAUSE THE LENGTH OF ITS PARSE TREE IN THE CATALOG IS ZERO
-820	THE SQL STATEMENT CANNOT BE PROCESSED BECAUSE catalog-table CONTAINS A VALUE THAT IS NOT VALID IN THIS RELEASE
-822	THE SQLDA CONTAINS AN INVALID DATA ADDRESS OR INDICATOR VARIABLE ADDRESS
-840	TOO MANY ITEMS RETURNED IN A SELECT OR INSERT LIST
-842	A CONNECTION TO location-name ALREADY EXISTS
-843	THE SET CONNECTION OR RELEASE STATEMENT MUST SPECIFY AN EXISTING CONNECTION
-870	THE NUMBER OF HOST VARIABLES IN THE STATEMENT IS NOT EQUAL TO THE NUMBER OF DESCRIPTORS
-900	THE SQL STATEMENT CANNOT BE EXECUTED BECAUSE THE APPLICATION PROCESS IS NOT CONNECTED TO AN APPLICATION SERVER
-901	UNSUCCESSFUL EXECUTION CAUSED BY A SYSTEM ERROR THAT DOES NOT PRECLUDE THE SUCCESSFUL EXECUTION OF SUBSEQUENT SQL STATEMENTS
-902	POINTER TO THE ESSENTIAL CONTROL BLOCK (CT/RDA) HAS VALUE 0, REBIND REQUIRED
-904	UNSUCCESSFUL EXECUTION CAUSED BY AN UNAVAILABLE RESOURCE. REASON reason-code, TYPE OF RESOURCE resource-type, AND RESOURCE NAME resource-name
-905	UNSUCCESSFUL EXECUTION DUE TO RESOURCE LIMIT BEING EXCEEDED, RESOURCE NAME = resource-name LIMIT = limit-amount1 CPU SECONDS (limit-amount2 SERVICE UNITS) DERIVED FROM limit-source
-906	THE SQL STATEMENT CANNOT BE EXECUTED BECAUSE THIS FUNCTION IS DISABLED DUE TO A PRIOR ERROR
-908	bind-type ERROR USING auth-id AUTHORITY BIND, REBIND OR AUTO-REBIND OPERATION IS NOT ALLOWED
-909	THE OBJECT HAS BEEN DELETED
-910	THE SQL STATEMENT CANNOT ACCESS AN OBJECT ON WHICH A DROP OR ALTER IS PENDING
-911	THE CURRENT UNIT OF WORK HAS BEEN ROLLED BACK DUE TO DEADLOCK OR TIMEOUT. REASON reason-code, TYPE OF RESOURCE resource-type, AND RESOURCE NAME resource-name
-913	UNSUCCESSFUL EXECUTION CAUSED BY DEADLOCK OR TIMEOUT. REASON CODE reason-code, TYPE OF RESOURCE resource-type, AND RESOURCE NAME resource-name
-917	BIND PACKAGE FAILED
-918	THE SQL STATEMENT CANNOT BE EXECUTED BECAUSE A CONNECTION HAS BEEN LOST
-919	A ROLLBACK OPERATION IS REQUIRED
-922	AUTHORIZATION FAILURE: error-type ERROR. REASON reason-code
-923	CONNECTION NOT ESTABLISHED: DB2 condition REASON reason-code, TYPE resource-type, NAME resource-name
-924	DB2 CONNECTION INTERNAL ERROR, function-code, return-code, reason-code
-925	COMMIT NOT VALID IN IMS/VS OR CICS ENVIRONMENT
-926	ROLLBACK NOT VALID IN IMS/VS OR CICS ENVIRONMENT
-927	THE LANGUAGE INTERFACE (LI) WAS CALLED WHEN THE CONNECTING ENVIRONMENT WAS NOT ESTABLISHED. THE PROGRAM SHOULD BE INVOKED UNDER THE DSN COMMAND
-929	FAILURE IN A DATA CAPTURE EXIT: token
-939	ROLLBACK REQUIRED DUE TO UNREQUESTED ROLLBACK OF A REMOTE SERVER
-947	THE SQL STATEMENT FAILED BECAUSE IT WILL CHANGE A TABLE DEFINED WITH DATA CAPTURE CHANGES, BUT THE DATA CANNOT BE PROPAGATED
-948	DISTRIBUTED OPERATION IS INVALID
-950	THE LOCATION NAME SPECIFIED IN THE CONNECT STATEMENT IS INVALID OR NOT LISTED IN THE COMMUNICATIONS DATABASE
-965	STORED PROCEDURE procname TERMINATED ABNORMALLY
-2001	THE NUMBER OF HOST VARIABLE PARAMETERS FOR A STORED PROCEDURE IS NOT EQUAL TO THE NUMBER OF EXPECTED HOST VARIABLE PARAMETERS. ACTUAL NUMBER sqldanum, EXPECTED NUMBER opnum
-30000	EXECUTION FAILED DUE TO A DISTRIBUTION PROTOCOL ERROR THAT WILL NOT AFFECT THE SUCCESSFUL EXECUTION OF SUBSEQUENT COMMANDS OR SQL STATEMENTS: REASON reason-code (sub-code)
-30020	EXECUTION FAILED DUE TO A DISTRIBUTION PROTOCOL ERROR THAT CAUSED DEALLOCATION OF THE CONVERSATION: REASON <reason-code (sub-code)>
-30021	EXECUTION FAILED DUE TO A DISTRIBUTION PROTOCOL ERROR THAT WILL AFFECT THE SUCCESSFUL EXECUTION OF SUBSEQUENT COMMANDS OR SQL STATEMENTS: MANAGER manager AT LEVEL level NOT SUPPORTED ERROR
-30030	COMMIT REQUEST WAS UNSUCCESSFUL, A DISTRIBUTION PROTOCOL VIOLATION HAS BEEN DETECTED, THE CONVERSATION HAS BEEN DEALLOCATED. ORIGINAL SQLCODE=original-sqlcode AND ORIGINAL SQLSTATE=original-sqlstate
-30040	EXECUTION FAILED DUE TO UNAVAILABLE RESOURCES THAT WILL NOT AFFECT THE SUCCESSFUL EXECUTION OF SUBSEQUENT COMMANDS OR SQL STATEMENTS. REASON <reason-code> TYPE OF RESOURCE <resource-type> RESOURCE NAME <resource-name> PRODUCT ID <pppvvrrm> RDBNAME <rdbname>
-30041	EXECUTION FAILED DUE TO UNAVAILABLE RESOURCES THAT WILL AFFECT THE SUCCESSFUL EXECUTION OF SUBSEQUENT COMMANDS AND SQL STATEMENTS REASON <reason-code> TYPE OF RESOURCE <resource-type> RESOURCE NAME <resource-name> PRODUCT ID <pppvvrrm> RDBNAME <rdbname>
-30050	<command-or-SQL-statement-type> COMMAND OR SQL STATEMENT INVALID WHILE BIND PROCESS IN PROGRESS
-30051	BIND PROCESS WITH SPECIFIED PACKAGE NAME AND CONSISTENCY TOKEN NOT ACTIVE
-30052	PROGRAM PREPARATION ASSUMPTIONS ARE INCORRECT
-30053	OWNER AUTHORIZATION FAILURE
-30060	RDB AUTHORIZATION FAILURE
-30061	RDB NOT FOUND
-30070	<command> COMMAND NOT SUPPORTED ERROR
-30071	<object-type> OBJECT NOT SUPPORTED ERROR
-30072	<parameter>:<subcode> PARAMETER NOT SUPPORTED ERROR
-30073	<parameter>:<subcode> PARAMETER VALUE NOT SUPPORTED ERROR
-30074	REPLY MESSAGE WITH codepoint (svrcod) NOT SUPPORTED ERROR
-30080	COMMUNICATION ERROR code (subcode)
-30090	REMOTE OPERATION INVALID FOR APPLICATION EXECUTION ENVIRONMENT

Useful List of SYSIBM tables

SYSCHECKDEP
Column references for CHECK constraints

SYSCHECKS
CHECK constraint specifications

SYSCHECKS2 (V7)
Information about CHECK constraints on DB2 Catalog tables created for V7 or later

SYSCOLAUTH
The UPDATE privileges held by DB2 users on table or view columns

SYSCOLDIST
Cardinality, frequent value, and non-padded frequent value distribution statistics for the first key column of an index key

SYSCOLDIST_HIST (V7)
Column distribution statistics history

SYSCOLSTATS
The partition statistics for selected columns

SYSCOLUMNS
Information about every column of every DB2 table and view

SYSCOLUMNS_HIST (V7)
Historical column statistics

SYSCONSTDEP
Information regarding columns that are dependent on CHECK constraints and user-defined defaults

SYSDATABASE
Information about every DB2 database

SYSDATATYPES
Information about the user-defined distinct types defined to the DB2 subsystem

SYSDBAUTH
Database privileges held by DB2 users

SYSDBRM
DBRM information only for DBRMs bound into DB2 plans

SYSDUMMY1
Contains no information; this table is for use in SQL statements requiring a table reference without regard to data content

SYSFIELDS
Information on field procedures implemented for DB2 tables

SYSFOREIGNKEYS
Information about all columns participating in foreign keys

SYSINDEXES
Information about every DB2 index

SYSINDEXES_HIST (V7)
Historical index statistics

SYSINDEXPART
Information about the physical structure and storage of every DB2 index

SYSINDEXPART_HIST (V7)
Historical index partition statistics

SYSINDEXSTATS
Partitioned index statistics by partition

SYSINDEXSTATS_HIST (V7)
Historical partitioned index statistics by partition

SYSKEYCOLUSE (V7)
Columns that participate in unique constraints (primary key or unique key) from the SYSTABCONST table

SYSKEYS
Information about every column of every DB2 index

SYSPACKAGE
Information about every package known to DB2

SYSPACKAUTH
Package privileges held by DB2 users

SYSPACKDEP
A cross-reference of DB2 objects required for DB2 packages

SYSPACKLIST
The package list for plans bound specifying packages

SYSPACKSTMT
All SQL statements contained in each DB2 package

SYSPARMS
Parameters for defined routines

SYSPROCEDURES
The stored procedures available to the DB2 subsystem

SYSRELS
The referential integrity information for every relationship defined to DB2

SYSRESAUTH
Resource privileges held by DB2 users

SYSROUTINEAUTH
Privileges held by DB2 users on routines

SYSROUTINES
Information about every routine (that is, user-defined functions and stored procedures) defined to the DB2 subsystem

SYSSCHEMAAUTH
Schema privileges granted to users

SYSSEQUENCEAUTH (V8)
Privileges held by DB2 users on SEQUENCE objects

SYSSEQUENCES (V7)
Information about identity columns and SEQUENCE objects

SYSSEQUENCESDEP (V8)
Records the dependencies of identity columns on tables

SYSSTMT
All SQL statements contained in each DB2 plan bound from a DBRM

SYSSYNONYMS
Information about every DB2 synonym

SYSTABAUTH
Table privileges held by DB2 users

SYSTABCONST (V7)
Information about every unique constraint (primary key or unique key) created in DB2 V7 or later

SYSTABLEPART
Information about the physical structure and storage of every DB2 table space

SYSTABLEPART_HIST (V7)
Historical table space partition statistics

SYSTABLES
Information about every DB2 table

SYSTABLES_HIST (V7)
Table statistics history

SYSTABLESPACE
Information about every DB2 table space

SYSTABSTATS
Partitioned table space statistics by partition

SYSTABSTATS_HIST (V7)
Historical partitioned table space statistics by partition

SYSTRIGGERS
Information about every trigger defined to the DB2 subsystem

SYSUSERAUTH
System privileges held by DB2 users

SYSVIEWS
Information about every DB2 views

GDG - A Basic Introduction

Introduction

• GDG stands for Generation Data Group. Here a group of files are related functionally or chronologically and can be accessed individually or as a group. These files share a unique name.
•  Every dataset in a GDG has a Generation number and version number. Eg.TESTGDG.DATA.G0001.V00
• Subsequent files are named by incrementing the Generation number(G0001-G9999).
• The current generation is referred a 0. The next generation as +1 an previous generation as -1
• Every time a new gen is created, it becomes the current gen and the current gen becomes the previous generation.
• All generations must be cataloged to keep track of the relative generation  numbers
• Maximum number of versions for a GDG is 255.
• Each generation can have same or different DCB parameters.

Why We Need IT:

1. Record keeping
2. Easy to relate data sets
3. Auto deletion of outdated datasets
4. No need to change the JCL every time.

How to define a GDG :

To use the GDG we have to define the GDG base / index.

//STEP10 EXEC PGM=IDCAMS
//SYSPRINT DD SYSOUT=*
//SYSIN DD *
            DEFINE GDG(NAME(TESTGDG.DATA.GDG) -
            LIMIT(10) -
            NOEMPTY -
            SCRATCH)
/*

Where,

• GDG name can range from 1-44
• LIMIT can range from 1-255

Various Other Options: 

Empty Vs NoEmpty

Empty:-This option will delete all the older generations if the limit of generations is reached

Example: If a GDG is defined with a limit of 10 and 11th generation is created then all the old 10 generations are deleted and the 11th generation is created.

NoEmpty:- This option will delete the oldest generation if the limit of generations is reached .

Example: If a GDG is defined with a limit of 10 and 11th generation is created then the 1^st  generation is  deleted and the 11th generation is created.

Scratch Vs NoScratch

Scratch:-This option will delete the generation completely if the dataset is deleted. 

NoScratch-> This option will uncatalog the dataset if the dataset is deleted.

Creating a GDG version:

GDG version can be created in same JCL by giving +1 to the GDG base name.

STEP1   EXEC PGM=IEFBR14                                 

INFILE  DD  DSN= TESTGDG.DATA.GDG (+1),   

        DISP=(NEW,CATLG,DELETE),                         

        UNIT=SYSDA,                                      

        SPACE=(CYL,(200,200),RLSE),                      

       DCB=(LRECL=2700,BLKSIZE=0,RECFM=FB)                

                                                         

Altering a GDG:

We can also change the attributes of a GDG using the ALTER keyword.

//STEP1   EXEC PGM=IDCAMS                      

//SYSPRINT DD SYSOUT=*                         

//SYSIN    DD *                                

    ALTER TESTGDG.DATA.GDG  NOSCRATCH     

/*                                             

Deleting a GDG generation:

//STEP1   EXEC PGM=IEFBR14                    

//DL       DD  DSN=TESTGDG.DATA.GDG(+1),      

//         DISP=(OLD,DELETE,DELTE)            

/*      

Deleting a GDG base /Index:

Two parameters for deleting the base .

1.PURGE:    Object will be deleted regardless of the expiration date.   

//SYSIN    DD  *                  

   DELETE TESTGDG.DATA.GDG PURGE    

2.FORCE: Object will be delete even though it is not empty.   

//SYSIN    DD  *                  

   DELETE TESTGDG.DATA.GDG FORCE

Ranking and Numbering for Records in DB2

DB2 supports ranking and numbering in much the same way that Oracle does.

The available functions are:

• ROW_NUMBER(), which simply numbers the returned rows sequentially
• RANK(), which ranks the results, but, in the case of a tie, gives the same number to each and leaves a gap to compensate
• DENSE_RANK() operates the same as RANK() but doesn't leave any gaps.

The difference in these three methods can be seen in Listing 4.

Listing 1. Ranking and Numbering Results

   select 
     client, MONTHEND, SALEVOL, 
     ROW_NUMBER() over (order by SALEVOL desc) AS RN, 
     RANK() over (order by SALEVOL desc) AS RANK, 
     DENSE_RANK() over (order by SALEVOL desc) AS DENSE
   from mysales 
   where MONTHEND=DATE('1997-11-30')
   order by RN
   
   CLIENT     MONTHEND   SALEVOL   RN  RANK DENSE
   CYRIX      11/30/1997    120     1     1     1
   BIG BLUE   11/30/1997    106     2     2     2
   EGGHEAD    11/30/1997    106     3     2     2
   DEVX       11/30/1997     80     4     4     3
   FIGTREE    11/30/1997     62     5     5     4
   ACME       11/30/1997     20     6     6     5

The results need not be returned in rank order. We may wish to show each client's rank while listing them alphabetically

Listing 2. Return Ranks in Any Order

   select
     CLIENT, SALEVOL,
     RANK() over (order by SALEVOL desc) AS RANK
   from mysales 
   where MONTHEND=DATE('1997-11-30') 
   order by CLIENT
   
   CLIENT     SALEVOL RANK
   ACME            20    6
   BIG BLUE       106    2
   CYRIX          120    1
   DEVX            80    4
   EGGHEAD        106    2
   FIGTREE         62    5

One application of ROW_NUMBER is to select a numbered range of rows from the middle of your results. For example, you could retrieve the next three clients following the top two as shown in Listing 6.

Listing 3. Selecting Rows by Number

   WITH ALLSALES AS 
      (SELECT CLIENT, SUM(SALEVOL) AS TOTALVOL,
      ROW_NUMBER() OVER
         (ORDER BY SUM(SALEVOL) DESC, CLIENT)
      AS RN
      FROM MYSALES 
      GROUP BY CLIENT) 
   SELECT 
      CLIENT, TOTALVOL, RN
   FROM ALLSALES
   WHERE RN BETWEEN 3 AND 5
   ORDER BY RN
   
   CLIENT     TOTALVOL  RN
   BIG BLUE       4781   3
   FIGTREE        3986   4
   ACME           3044   5

Selecting first few rows in DB2

DB2's method of performing a Top-N query is the FETCH FIRST clause. You can append these variations to a regular SELECT query:

• FETCH FIRST ROW ONLY
• FETCH FIRST 1 ROW ONLY
• FETCH FIRST integer ROWS ONLY

Interestingly, you can also use

• FETCH FIRST 1 ROWS ONLY
• FETCH FIRST 5 ROW ONLY

They aren't as nice grammatically, but they make it easier to generate queries automatically—you don't have to worry about whether to say ROW or ROWS.


Now, we can ask for a single record as follows:

Listing 1. Return a Single Row

   SELECT * FROM MYSALES FETCH FIRST ROW ONLY
   
   CLIENT     MONTHEND   SALEVOL 
   DEVX       03/31/1998     100

We have retrieved one row, but there's no way to know ahead of time which row it will be.(HINT: This does give us a handy way to remind ourselves what fields are in a table, with a row of sample data as a bonus!)

To find our top two clients, use the code in Listing 3.

Listing 2: Show Top Two Clients

   SELECT CLIENT, SUM(SALEVOL) AS TOTALVOL
   FROM MYSALES 
   GROUP BY CLIENT 
   ORDER BY SUM(SALEVOL) DESC 
   FETCH FIRST 2 ROWS ONLY
   
   CLIENT     TOTALVOL   
   DEVX           5785
   EGGHEAD        5341

INSPECT in COBOL

The INSPECT verb has two options, TALLYING and REPLACING. You can do one or the other or both. If both are done, the TALLYING IS DONE BEFORE THE replacing. Some versions of the INSPECT require that all the literals be in quotes. This may call for a redefination if the field is numeric.
In using the TALLYING format of the INSPECT, you are tallying into a field that is a counter. This field must be initialized to 0 before the inspect is done. Unsually this is done by a MOVE 0 to whatever the counter is called.

The TALLYING option of the INSPECT has multiple options - in fact, some versions have options beyond those required by the COBOL specifications:

• You can tally ALL of something
• You can tally just the LEADING occurances of something
• You can just tally CHARACTERS
• You can tally BEFORE or AFTER an INITIAL specified character

Example #1: This statement counts the number of occurrences of the digit 5 until the first space is encountered. If FLA = 256545 67 then after the INSPECT, CTRA would equal 3.
MOVE 0 TO CTRA.
INSPECT FLDA TALLY CTRA
    FOR ALL "5" BEFORE INTIAL SPACE.

Example #2: This statement counts the number of spaces in the field FLDB.
If FLB = 4 5 6 1 2 then after the INSPECT, CTRB would equal 4 since there is a space between each number.
MOVE 0 TO CTRB.
INSPECT FLDB TALLYING CTRB
    FOR ALL SPACES.

Example #3: In this example, the count of all characters before the first space is tallied. If FLDC is equal to 16AB5 6 then CTRC will contain the number 5 since 1, 6, A, B, and 5 proceed the space before the 6.
MOVE 0 TO CTRC.
INSPECT FLDC TALLYING CTRC
    FOR CHARACTERS BEFORE INITIAL SPACE.

Example #4: This statement counts all of the leading zeros in a field. Embedded or trailing zeros are counted. IF FLDD is equal to 00090020, then CTRD is equal to 3.
MOVE 0 TO CTRD.
INSPECT FLDD TALLYING CTRD
    FOR LEADING ZEROS.

Example #5: This statement counts all zeros in the field. IF FLDE is equal to 00090020, then CTRE will be equal to 6.
MOVE 0 TO CTRE.
INSPECT FLDE TALLYING CTRE
    FOR ALL ZEROS.
Example #6: This statement tallies for all zeroes that proceed the initial 2 In this case, CTRF will be 5. FLDF is equal to 00090020.
INSPECT FLDF TALLYING CTRF
    FOR ALL ZEROS BEFORE INITIAL 2.

Example #7: This statement counts the spaces before the number 5. In this example CTRG will be 3. FLDG has three spaces and then the number 1257 ( 1257).
INSPECT FLDG TALLYING CTRG
    FOR LEADING SPACE BEFORE INITIAL 5.

Example #8: This statement will replace all B with G. FLDH will start as ABCBDFB and will become AGCGDFG.
INSPECT FLDH REPLACING ALL "B" BY "G".

Example #9: This replace will change all A to X. This means that AAABBAAA will become XXXBBAAA.

INSPECT FLDI REPLACING CHARACTERS BY "X"
    BEFORE INITIAL "B".

Example #10: This will replace the first X with a 5. There ACXDGXB will become AC5DGXB.

INSPECT FLDJ REPLACING FIRST "X" BY "5".

COBOL Numeric Data Types - COMP

The following common COBOL data types are discussed below:

• Binary
• Computational (comp)
• Comp-1
• Comp-2
• Comp-3
• Packed Decimal

BINARY
Specified for binary data items. Such items have a decimal equivalent consisting of the decimal digits 0 through 9, plus a sign. Negative numbers are represented as the two's complement of the positive number with the same absolute value.  The amount of storage occupied by a binary item depends on the number  of decimal digits defined in its PICTURE clause:

Digits in PICTURE Clause Storage Occupied
1 through 4 2 bytes (halfword)
5 through 9 4 bytes (fullword)
10 through 18 8 bytes (doubleword) │
The leftmost bit of the storage area is the operational sign.

PACKED-DECIMAL
Specified for internal decimal items. Such an item appears in storage  in packed decimal format. There are 2 digits for each character  position, except for the trailing character position, which is  occupied by the low-order digit and the sign. Such an item can  contain any of the digits 0 through 9, plus a sign, representing a  value not exceeding 18 decimal digits.

The sign representation uses the same bit configuration as the 4-bit  sign representation in zoned decimal fields.

COMPUTATIONAL or COMP
Representation of the COMPUTATIONAL phrase is system-dependent and is  normally assigned to representations that yield the greatest  efficiency when arithmetic operations are performed on that system.

COMPUTATIONAL-1 or COMP-1
Specified for internal floating-point items (single precision).  COMP-1 items are 4 bytes long. The sign is contained in the first bit  of the leftmost byte and the exponent is contained in the remaining 7  bits. The last 3 bytes contain the mantissa.

COMPUTATIONAL-2 or COMP-2
Specified for internal floating-point items (double precision). COMP-2 items are 8 bytes long. The sign is contained in the first bit  of the leftmost byte and the remaining 7 bits contain the exponent.  The remaining 7 bytes contain the mantissa.

COMPUTATIONAL-3 or COMP-3 (internal decimal)
For VS COBOL II, this is the equivalent of PACKED-DECIMAL.

COMPUTATIONAL-4 or COMP-4 (binary)
For VS COBOL II this is the equivalent of BINARY.

Special Searching

A picture string in a FIND, CHANGE, or EXCLUDE command allows you to search for a particular kind of character without regard for the specific character involved. 

You can use special characters within the picture string to represent the kind of character to be found, as follows:

They can be used in REXX / ISREDIT macros too.  

String Meaning

P'='   Any character
P'¬'   Any character that is not a blank
P'.'    Any character that cannot be displayed
P'#'   Any numeric character, 0-9
P'-'    Any non-numeric character
P'@'   Any alphabetic character, uppercase or lowercase
P'c'    Any lowercase alphabetic character
P'>'   Any uppercase alphabetic character
P'$'   Any special character, neither alphabetic nor numeric.