tso tips

2010-02-16T10:57:00.000-08:00

List of SYSIBM tables

select * from sysibm.systables where CREATOR = 'SYSIBM' ;

DSNRLST01
INDOUBT
IPLIST
IPNAMES
JSPOUT
LOCATIONS
LULIST
LUMODES
LUNAMES
MODESELECT
SQLTABLETYPES
SQLTYPEINFO
SQTCOLPRIVILEGES
SQTCOLUMNS
SQTFOREIGNKEYS
SQTPRIMARYKEYS
SQTPROCEDURECOLS
SQTPROCEDURES
SQTSPECIALCOLUMNS
SQTSTATISTICS
SQTTABLEPRIVILEGES
SQTTABLES
SQTUDTS
SYSAUXRELS
SYSCHECKDEP
SYSCHECKS
SYSCHECKS2
SYSCOLAUTH
SYSCOLDIST
SYSCOLDISTSTATS
SYSCOLDIST_HIST
SYSCOLSTATS
SYSCOLUMNS
SYSCOLUMNS_HIST
SYSCONSTDEP
SYSCOPY
SYSDATABASE
SYSDATATYPES
SYSDBAUTH
SYSDBRM
SYSDUMMY1
SYSDUMMYA
SYSDUMMYE
SYSDUMMYU
SYSFIELDS
SYSFOREIGNKEYS
SYSINDEXES
SYSINDEXES_HIST
SYSINDEXPART
SYSINDEXPART_HIST
SYSINDEXSTATS
SYSINDEXSTATS_HIST
SYSJARCLASS_SOURCE
SYSJARCONTENTS
SYSJARDATA
SYSJAROBJECTS
SYSJAVAOPTS
SYSKEYCOLUSE
SYSKEYS
SYSLOBSTATS
SYSLOBSTATS_HIST
SYSOBDS
SYSPACKAGE
SYSPACKAUTH
SYSPACKDEP
SYSPACKLIST
SYSPACKSTMT
SYSPARMS
SYSPKSYSTEM
SYSPLAN
SYSPLANAUTH
SYSPLANDEP
SYSPLSYSTEM
SYSPRINT
SYSPSM
SYSPSMOPTS
SYSPSMOUT
SYSRELS
SYSRESAUTH
SYSROUTINEAUTH
SYSROUTINES
SYSROUTINES_OPTS
SYSROUTINES_SRC
SYSSCHEMAAUTH
SYSSEQUENCEAUTH
SYSSEQUENCES
SYSSEQUENCESDEP
SYSSTMT
SYSSTOGROUP
SYSSTRINGS
SYSSYNONYMS
SYSTABAUTH
SYSTABCONST
SYSTABLEPART
SYSTABLEPART_HIST
SYSTABLES
SYSTABLESPACE
SYSTABLES_HIST
SYSTABSTATS
SYSTABSTATS_HIST
SYSTRIGGERS
SYSUSERAUTH
SYSVIEWDEP
SYSVIEWS
SYSVLTREE
SYSVOLUMES
SYSVTREE
UINDEXES
USERNAMES

IPLIST (V8)
Associates multiple IP addresses to a given LOCATION

IPNAMES
Contains the LUs associated with other systems accessible to the local DB2 subsystem

LOCATIONS
Contains distributed location information for every accessible remote server

LULIST
Contains the list of LUNAMEs for a given distributed location (when multiple LUNAMEs are associated with a single location)

LUMODES
Information on distributed conversation limits

LUNAMES
Contains information for every SNA client or server that communicates with the DB2 subsystem

MODESELECT
Information assigning mode names to conversations supporting outgoing SQL requests

SYSAUXRELS
Information on the auxiliary tables required for LOB columns

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

SYSCOLDISTSTATS
The non-uniform distribution statistics for the ten most frequently occurring values for the first key column in a partitioned index

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

SYSCOPY
Information on the execution of DB2 utilities required by DB2 recovery

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

SYSJARCONTENTS (V7)
Java class source for installed JAR

SYSJARDATA (V7)
Auxiliary table for the BLOB data from SYSJAROBJECTS

SYSJAROBJECTS (V7)
The contents of the installed JAR

SYSJARCLASS_SOURCE (V7)
Auxiliary table for the CLOB data from SYSJARCONTENTS

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

SYSLINKS
Information about the links between DB2 Catalog tables

SYSLOBSTATS
Statistical information for LOB table spaces

SYSLOBSTATS_HIST (V7)
Historical LOB statistics

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

SYSPKSYSTEM
The systems (such as CICS, IMS, or batch) enabled for DB2 packages

SYSPLAN
Information about every plan known to DB2SYSPLANSYSPLANAUTHPlan privileges held by DB2 users

SYSPLANDEP
A cross-reference of DB2 objects required by DB2 plans

SYSPLSYSTEM
The systems (such as CICS, IMS, or batch) enabled for DB2 plans

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

SYSROUTINES_OPTS (V7)
Information about the options used by DB2-generated routines

SYSROUTINES_SRC (V7)
The source code for routines generated by DB2

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

SYSSTOGROUP
Information about every DB2 storage group

SYSSTRINGS
Character conversion information

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

SYSVIEWDEP
A cross-reference of DB2 objects required by DB2 views

SYSVIEWS
The SQL CREATE VIEW statement for every DB2 view

SYSVLTREE
A portion of the internal representation of complex or long views

SYSVOLUMES
A cross-reference of DASD volumes assigned to DB2 storage groups

SYSVTREE
The first 4000 bytes of the internal representation of the view; the remaining portion of longer or complex views is stored in SYSVLTREE

USERNAMES
Outbound and inbound ID translation information

selEct GRANTEE ,TTNAME , GRANTOR, INSERTAUTH, SELECTAUTH, UPDATEAUTH, DELETEAUTH,
GRANTEETYPE , DBNAME, SCREATOR, STNAME , TCREATOR
from sysibm.SYSTABAUTH where TTNAME ='VRECON_DETAIL' AND TCREATOR = 'SIDC3008' ;

sql error code

2010-02-13T08:33:00.000-08:00

These are the most common SQL CODES.

SQLCODES for DB2 Version 8
from the IBM manual GC18-7422-0 04/06/05

Manuals and Tutorials on SQL and DB2

Buy Books on SQL and DB2 so you will know what causes these SQLCODES

VSAM and QSAM File Status Codes/keys

Abend codes / System Completion Codes

SQLCODE +100 SQLSTATE 02000
Row not found or end of cursor

SQLCODE -102 String constant is too long

SQLCODE -117 The number of values you are trying to INSERT
does not match the number of columns

SQLCODE -180 SQLSTATE 22007
Bad data in Date/Time/Timestamp
String representation of DATE, TIME, TIMESTAMP is invalid

SQLCODE -181 SQLSTATE 22007
Bad data in Date/Time/Timestamp
Value for DATE, TIME, TIMESTAMP is invalid

SQLCODE -199 Illegal use of the specified keyword.

SQLCODE -204 Object not defined to DB2

SQLCODE -205 Column name not in specified table

SQLCODE -206 Column does not exist in any table in the SELECT

SQLCODE -216
You need to use the same number of expressions on both sides of the comparison.
when using multiple operands in a comparison,
Correct example:
WHERE (E.SALARY, E.COMM) IN
(SELECT S.PAY, S.COMMISSION
Incorrect example:
WHERE (E.SALARY, E.COMM, E.BONUS) IN
(SELECT S.PAY, S.COMMISSION

SQLCODE +222 Trying to fetch a row that fell through a DELETE hole

SQLCODE +223 Trying to fetch a row that fell through an UPDATE hole

SQLCODE -224 FETCH cannot make an INSENSITIVE cursor SENSITIVE

SQLCODE -229 The locale specified in a SET LOCALE statement was not found.

SQLCODE +231 FETCH
but you did a BEFORE or AFTER and you are not on a valid row

SQLCODE +304 Value cannot be assigned to this host variable because it is out of range

SQLCODE -305 Null indicator needed

SQLCODE -311 Varchar, insert or update.
You didn’t set the -LEN field with the right data length

SQLCODE -482 he procedure returned no locators

SQLCODE -501 Cursor not open on FETCH

SQLCODE -502 Opening cursor that is already open

SQLCODE -503 Updating column which needs to be specified in FOR UPDATE OF

SQLCODE -530 SQLSTATE 23503
Referential integrity prevents the INSERT/UPDATE

SQLCODE -532 SQLSTATE 23504
Referential integrity (DELETE RESTRICT rule) prevents the DELETE

SQLCODE -536 SQLSTATE 42914
Referential integrity (DELETE RESTRICT rule) prevents the DELETE

SQLCODE -545 SQLSTATE 23513
Check constraint prevents the INSERT/UPDATE

SQLCODE -747 The table specified is not available
because tables and/or indexes need to be created.

SQLCODE +802 The null indicator was set to -2
because an arithmetic statement didn't work.

SQLCODE -803 SQLSTATE 23505
Duplicate key on insert or update

SQLCODE -805
DBRM or package not found in plan
Is plan name correct?
See this web page

SQLCODE -811 More than one row retrieved in SELECT INTO

SQLCODE -818 Plan and program: timestamp mismatch

SQLCODE -904 SQLSTATE 57011
Unavailable resource. Someone is locking the data you need
you may choose to terminate the program

SQLCODE -911 SQLSTATE 40000
Deadlock or timeout. Rollback has been done.

SQLCODE -913 SQLSTATE 40502
Your program was the victim of a deadlock or timeout.
NO rollback has been done.
You should do a ROLLBACK.

SQLCODE -922 Authorization needed

SQLCODE -927 The language interface was called but no connection had been

SQLCODE reference: All the sqlcodes from -991 to +883 in DB2 Version 7.
Top of Page

SQLCODE -991, Error: CALL ATTACH WAS UNABLE TO ESTABLISH AN IMPLICIT CONNECT OR OPEN TO DB2. RC1= RC2=

SQLCODE -981, Error: THE SQL STATEMENT FAILED BECAUSE THE RRSAF CONNECTION IS NOT IN A STATE THAT ALLOWS SQL OPERATIONS, REASON

SQLCODE -950, Error: THE LOCATION NAME SPECIFIED IN THE CONNECT STATEMENT IS INVALID OR NOT LISTED IN THE COMMUNICATIONS DATABASE

SQLCODE -948, Error: DISTRIBUTED OPERATION IS INVALID

SQLCODE -947, Error: THE SQL STATEMENT FAILED BECAUSE IT WILL CHANGE A TABLE DEFINED WITH DATA CAPTURE
CHANGES, BUT THE DATA CANNOT BE PROPAGATED

SQLCODE -939, Error: ROLLBACK REQUIRED DUE TO UNREQUESTED ROLLBACK OF A REMOTE SERVER

SQLCODE -929, Error: FAILURE IN A CHANGED DATA CAPTURE EXIT:

SQLCODE -927, Error: THE LANGUAGE INTERFACE (LI) WAS CALLED WHEN THE CONNECTING ENVIRONMENT WAS NOT
ESTABLISHED. THE PROGRAM SHOULD BE INVOKED UNDER THE DSN COMMAND

SQLCODE -926, Error: ROLLBACK NOT VALID IN IMS OR CICS ENVIRONMENT

SQLCODE -925, Error: COMMIT NOT VALID IN IMS OR CICS ENVIRONMENT

SQLCODE -924, Error: DB2 CONNECTION INTERNAL ERROR, , ,

SQLCODE -923, Error: CONNECTION NOT ESTABLISHED: DB2 , REASON , TYPE , NAME

SQLCODE -922, Error: AUTHORIZATION FAILURE: ERROR. REASON

SQLCODE -919, Error: A ROLLBACK OPERATION IS REQUIRED

SQLCODE -918, Error: THE SQL STATEMENT CANNOT BE EXECUTED BECAUSE A CONNECTION HAS BEEN LOST

SQLCODE -917, Error: BIND PACKAGE FAILED

SQLCODE -913, Error: UNSUCCESSFUL EXECUTION CAUSED BY DEADLOCK OR TIMEOUT. REASON CODE , TYPE OF RESOURCE , AND RESOURCE NAME

SQLCODE -911, Error: THE CURRENT UNIT OF WORK HAS BEEN ROLLED BACK DUE TO DEADLOCK OR TIMEOUT. REASON ,
TYPE OF RESOURCE , AND RESOURCE NAME

SQLCODE -910, Error: THE SQL STATEMENT CANNOT ACCESS AN OBJECT ON WHICH A DROP OR ALTER IS PENDING

SQLCODE -909, Error: THE OBJECT HAS BEEN DELETED OR ALTERED

SQLCODE -908, Error: ERROR USING AUTHORITY. BIND, REBIND, OR AUTO-REBIND OPERATION IS NOT ALLOWED

SQLCODE -906, Error: THE SQL STATEMENT CANNOT BE EXECUTED BECAUSE THIS FUNCTION IS DISABLED DUE TO A PRIOR ERROR

SQLCODE -905, Error: UNSUCCESSFUL EXECUTION DUE TO RESOURCE LIMIT BEING EXCEEDED, RESOURCE NAME = LIMIT =
CPU SECONDS ( SERVICE UNITS) DERIVED FROM

SQLCODE -904, Error: UNSUCCESSFUL EXECUTION CAUSED BY AN UNAVAILABLE RESOURCE. REASON , TYPE OF RESOURCE , AND RESOURCE NAME

SQLCODE -902, Error: POINTER TO ESSENTIAL CONTROL BLOCK(RDA/CT) HAS VALUE 0, REBIND REQUIRED

SQLCODE -901, Error: UNSUCCESSFUL EXECUTION CAUSED BY A SYSTEM ERROR THAT DOES NOT PRECLUDE THE SUCCESSFUL
EXECUTION OF SUBSEQUENT SQL STATEMENTS

SQLCODE -900, Error: THE SQL STATEMENT CANNOT BE EXECUTED BECAUSE THE APPLICATION PROCESS IS NOT CONNECTED
TO AN APPLICATION SERVER

SQLCODE -882, Error: SAVEPOINT DOES NOT EXIST

SQLCODE -881, Error: A SAVEPOINT WITH NAME ALREADY EXISTS, BUT THIS SAVEPOINT NAME CANNOT BE REUSED

SQLCODE -880, Error: SAVEPOINT DOES NOT EXIST OR IS INVALID IN THIS CONTEXT

SQLCODE -879, Error: CREATE OR ALTER STATEMENT FOR CANNOT DEFINE A COLUMN, DISTINCT TYPE, FUNCTION OR
STORED PROCEDURE PARAMETER AS MIXED OR GRAPHIC WITH ENCODING SCHEME

SQLCODE -878, Error: THE PLAN_TABLE USED FOR EXPLAIN CANNOT BE ASCII

SQLCODE -877, Error: CCSID ASCII IS NOT ALLOWED FOR THIS DATABASE OR TABLE SPACE

SQLCODE -876, Error: CANNOT BE CREATED, REASON

SQLCODE -875, Error: CANNOT BE USED WITH THE ASCII DATA REFERENCED

SQLCODE -874, Error: THE ENCODING SCHEME SPECIFIED FOR THE MUST BE THE SAME AS THE CONTAINING TABLE SPACE OR OTHER PARAMETERS

SQLCODE -873, Error: DATA ENCODED WITH DIFFERENT CCSIDS CANNOT BE REFERENCED IN THE SAME SQL STATEMENT

SQLCODE -872, Error: A VALID CCSID HAS NOT YET BEEN SPECIFIED FOR THIS SUBSYSTEM

SQLCODE -870, Error: THE NUMBER OF HOST VARIABLES IN THE STATEMENT IS NOT EQUAL TO THE NUMBER OF DESCRIPTORS

SQLCODE -867, Error: INVALID SPECIFICATION OF A ROWID COLUMN

SQLCODE -846, Error: INVALID SPECIFICATION OF AN IDENTITY COLUMN

SQLCODE -843, Error: THE SET CONNECTION OR RELEASE STATEMENT MUST SPECIFY AN EXISTING CONNECTION

SQLCODE -842, Error: A CONNECTION TO x ALREADY EXISTS

SQLCODE -840, Error: TOO MANY ITEMS RETURNED IN A SELECT OR INSERT LIST

SQLCODE -822, Error: THE SQLDA CONTAINS AN INVALID DATA ADDRESS OR INDICATOR VARIABLE ADDRESS

SQLCODE -820, Error: THE SQL STATEMENT CANNOT BE PROCESSED BECAUSE CONTAINS A VALUE THAT IS NOT VALID IN THIS RELEASE

SQLCODE -819, Error: THE VIEW CANNOT BE PROCESSED BECAUSE THE LENGTH OF ITS PARSE TREE IN THE CATALOG IS ZERO

SQLCODE -818, Error: THE PRECOMPILER-GENERATED TIMESTAMP IN THE LOAD MODULE IS DIFFERENT FROM THE BIND
TIMESTAMP BUILT FROM THE DBRM

SQLCODE -817, Error: THE SQL STATEMENT CANNOT BE EXECUTED BECAUSE THE STATEMENT WILL RESULT IN A PROHIBITED UPDATE OPERATION

SQLCODE -815, Error: A GROUP BY OR HAVING CLAUSE IS IMPLICITLY OR EXPLICITLY SPECIFIED IN A SUBSELECT OF A
BASIC PREDICATE OR A SET CLAUSE OF AN UPDATE STATEMENT

SQLCODE -812, Error: 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 USING PLAN

SQLCODE -811, Error: THE RESULT OF AN EMBEDDED SELECT STATEMENT OR A SUBSELECT IN THE SET CLAUSE OF AN
UPDATE STATEMENT IS A TABLE OF MORE THAN ONE ROW, OR THE RESULT OF A SUBQUERY OF A BASIC PREDICATE IS MORE THAN VALUE

SQLCODE -808, Error: THE CONNECT STATEMENT IS NOT CONSISTENT WITH THE FIRST CONNECT STATEMENT

SQLCODE -807, Error: ACCESS DENIED: PACKAGE IS NOT ENABLED FOR ACCESS FROM

SQLCODE -805, Error: DBRM OR PACKAGE NAME NOT FOUND IN PLAN . REASON

SQLCODE -804, Error: AN ERROR WAS FOUND IN THE APPLICATION PROGRAM INPUT PARAMETERS FOR THE SQL STATEMENT, REASON

SQLCODE -803, Error: AN INSERTED OR UPDATED VALUE IS INVALID BECAUSE INDEX IN INDEX SPACE CONSTRAINS
COLUMNS OF THE TABLE SO NO TWO ROWS CAN CONTAIN DUPLICATE VALUES IN THOSE COLUMNS. RID OF EXISTING ROW IS X''

SQLCODE -802, Error: EXCEPTION ERROR HAS OCCURRED DURING OPERATION ON DATA, POSITION

SQLCODE -798, Error: YOU CANNOT INSERT A VALUE INTO A COLUMN THAT IS DEFINED WITH THE OPTION GENERATED ALWAYS. COLUMN NAME

SQLCODE -797, Error: ATTEMPT TO CREATE TRIGGER WITH AN UNSUPPORTED TRIGGERED SQL STATEMENT

SQLCODE -771, Error: INVALID SPECIFICATION OF A ROWID COLUMN

SQLCODE -770, Error: TABLE CANNOT HAVE A LOB COLUMN UNLESS IT ALSO HAS A ROWID COLUMN

SQLCODE -769, Error: SPECIFICATION OF CREATE AUX TABLE DOES NOT MATCH THE CHARACTERISTICS OF THE BASE TABLE

SQLCODE -768, Error: AN AUXILIARY TABLE ALREADY EXISTS FOR THE SPECIFIED COLUMN OR PARTITION

SQLCODE -767, Error: MISSING OR INVALID COLUMN SPECIFICATION FOR INDEX

SQLCODE -766, Error: THE OBJECT OF A STATEMENT IS AN AUXILIARY TABLE FOR WHICH THE REQUESTED OPERATION IS NOT PERMITTED

SQLCODE -765, Error: TABLE IS NOT COMPATIBLE WITH DATABASE

SQLCODE -764, Error: A LOB TABLE SPACE AND ITS ASSOCIATED BASE TABLE SPACE MUST BE IN THE SAME DATABASE

SQLCODE -763, Error: INVALID TABLE SPACE NAME

SQLCODE -752, Error: THE CONNECT STATEMENT IS INVALID BECAUSE THE PROCESS IS NOT IN THE CONNECTABLE STATE

SQLCODE -751, Error: (SPECIFIC NAME ) ATTEMPTED TO EXECUTE AN SQL STATEMENT THAT IS NOT ALLOWED

SQLCODE -750, Error: THE SOURCE TABLE CANNOT BE RENAMED BECAUSE IT IS REFERENCED IN EXISTING VIEW
DEFINITIONS OR TRIGGER DEFINITIONS

SQLCODE -748, Error: AN INDEX ALREADY EXISTS ON AUXILIARY TABLE

SQLCODE -747, Error: TABLE IS NOT AVAILABLE UNTIL THE AUXILIARY TABLES AND INDEXES FOR ITS EXTERNALLY
STORED COLUMNS HAVE BEEN CREATED

SQLCODE -746, Error: THE SQL STATEMENT IN AN EXTERNAL FUNCTION, TRIGGER, OR IN STORED PROCEDURE VIOLATES
THE NESTING SQL RESTRICTION

SQLCODE -742, Error: DSNDB07 IS THE IMPLICIT WORK FILE DATABASE

SQLCODE -741, Error: A DATABASE IS ALREADY DEFINED FOR MEMBER

SQLCODE -740, Error: FUNCTION IS DEFINED WITH THE OPTION MODIFIES SQL DATA WHICH IS NOT VALID IN THE
CONTEXT IN WHICH IT WAS INVOKED

SQLCODE -739, Error: ALTER FUNCTION FAILED BECAUSE FUNCTIONS CANNOT MODIFY DATA WHEN THEY ARE PROCESSED IN PARALLEL

SQLCODE -737, Error: IMPLICIT TABLE SPACE NOT ALLOWED

SQLCODE -736, Error: INVALID OBID SPECIFIED

SQLCODE -735, Error: DATABASE CANNOT BE ACCESSED BECAUSE IT IS NO LONGER A SHARED DATABASE

SQLCODE -734, Error: THE ROSHARE ATTRIBUTE OF A DATABASE CANNOT BE ALTERED FROM ROSHARE READ

SQLCODE -733, Error: 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

SQLCODE -732, Error: 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

SQLCODE -731, Error: USER-DEFINED DATASET MUST BE DEFINED WITH SHAREOPTIONS (1,3)

SQLCODE -730, Error: THE PARENT OF A TABLE IN A READ-ONLY SHARED DATABASE MUST ALSO BE A TABLE IN A
READ-ONLY SHARED DATABASE

SQLCODE -729, Error: A STORED PROCEDURE SPECIFYING COMMIT ON RETURN CANNOT BE THE TARGET OF A NESTED CALL STATEMENT

SQLCODE -728, Error: DATA TYPE IS NOT ALLOWED IN DB2 PRIVATE PROTOCOL PROCESSING

SQLCODE -726, Error: BIND ERROR ATTEMPTING TO REPLACE PACKAGE = . THERE ARE ENABLE OR DISABLE ENTRIES
CURRENTLY ASSOCIATED WITH THE PACKAGE

SQLCODE -725, Error: THE SPECIAL REGISTER AT LOCATION WAS SUPPLIED AN INVALID VALUE

SQLCODE -724, Error: THE ACTIVATION OF THE OBJECT WOULD EXCEED THE MAXIMUM LEVEL OF INDIRECT SQL CASCADING

SQLCODE -723, Error: AN ERROR OCCURRED IN A TRIGGERED SQL STATEMENT IN TRIGGER , SECTION NUMBER .
INFORMATION RETURNED: SQLCODE , SQLSTATE , AND MESSAGE TOKENS

SQLCODE -722, Error: ERROR USING AUTHORITY PACKAGE DOES NOT EXIST

SQLCODE -721, Error: BIND ERROR FOR PACKAGE = CONTOKEN = ''X IS NOT UNIQUE SO IT CANNOT BE CREATED

SQLCODE -720, Error: BIND ERROR, ATTEMPTING TO REPLACE PACKAGE = WITH VERSION = BUT THIS VERSION ALREADY EXISTS

SQLCODE -719, Error: BIND ADD ERROR USING AUTHORITY PACKAGE ALREADY EXISTS

SQLCODE -718, Error: REBIND OF PACKAGE FAILED BECAUSE IBMREQD OF IS INVALID

SQLCODE -717, Error: FOR WITH MARK FAILED BECAUSE DEPENDS ON FUNCTIONS OF THE RELEASE FROM WHICH FALLBACK HAS OCCURRED

SQLCODE -716, Error: PROGRAM PRECOMPILED WITH INCORRECT LEVEL FOR THIS RELEASE

SQLCODE -715, Error: PROGRAM WITH MARK FAILED BECAUSE IT DEPENDS ON FUNCTIONS OF THE RELEASE FROM WHICH FALLBACK HAS OCCURRED

SQLCODE -713, Error: THE REPLACEMENT VALUE FOR IS INVALID

SQLCODE -697, Error: OLD OR NEW CORRELATION NAMES ARE NOT ALLOWED IN A TRIGGER DEFINED WITH THE FOR EACH
STATEMENT CLAUSE. OLD_TABLE OR NEW_TABLE NAMES ARE NOT ALLOWED IN A TRIGGER WITH THE BEFORE CLAUSE

SQLCODE -696, Error: THE DEFINITION OF TRIGGER INCLUDES AN INVALID USE OF CORRELATION NAME OR TRANSITION TABLE NAME . REASON CODE=

SQLCODE -694, Error: THE DDL STATEMENT CANNOT BE EXECUTED BECAUSE A DROP IS PENDING ON THE DDL REGISTRATION TABLE

SQLCODE -693, Error: THE COLUMN IN DDL REGISTRATION TABLE OR INDEX IS NOT DEFINED PROPERLY

SQLCODE -692, Error: THE REQUIRED UNIQUE INDEX FOR DDL REGISTRATION TABLE DOES NOT EXIST

SQLCODE -691, Error: THE REQUIRED REGISTRATION TABLE DOES NOT EXIST

SQLCODE -690, Error: THE STATEMENT IS REJECTED BY DATA DEFINITION CONTROL SUPPORT. REASON

SQLCODE -689, Error: TOO MANY COLUMNS DEFINED FOR A DEPENDENT TABLE

SQLCODE -688, Error: INCORRECT DATA RETURNED FROM FIELD PROCEDURE, ,

SQLCODE -687, Error: FIELD TYPES INCOMPARABLE

SQLCODE -686, Error: COLUMN DEFINED WITH A FIELD PROCEDURE CAN NOT COMPARE WITH ANOTHER COLUMN WITH
DIFFERENT FIELD PROCEDURE

SQLCODE -685, Error: INVALID FIELD TYPE,

SQLCODE -684, Error: THE LENGTH OF LITERAL LIST BEGINNING IS TOO LONG

SQLCODE -683, Error: THE SPECIFICATION FOR COLUMN, DISTINCT TYPE, FUNCTION, OR PROCEDURE CONTAINS INCOMPATIBLE CLAUSES

SQLCODE -682, Error: FIELD PROCEDURE COULD NOT BE LOADED

SQLCODE -681, Error: COLUMN IN VIOLATION OF INSTALLATION DEFINED FIELD PROCEDURE RT: , RS: , MSG:

SQLCODE -680, Error: TOO MANY COLUMNS SPECIFIED FOR A TABLE, VIEW, OR TABLE FUNCTION

SQLCODE -679, Error: THE OBJECT CANNOT BE CREATED BECAUSE A DROP IS PENDING ON THE OBJECT

SQLCODE -678, Error: THE LITERAL SPECIFIED FOR THE INDEX LIMIT KEY MUST CONFORM TO THE DATA TYPE OF THE CORRESPONDING COLUMN

SQLCODE -677, Error: INSUFFICIENT VIRTUAL STORAGE FOR BUFFERPOOL EXPANSION

SQLCODE -676, Error: ONLY A 4K PAGE BUFFERPOOL CAN BE USED FOR AN INDEX

SQLCODE -672, Error: OPERATION DROP NOT ALLOWED ON TABLE

SQLCODE -671, Error: THE BUFFERPOOL ATTRIBUTE OF THE TABLE SPACE CANNOT BE ALTERED AS SPECIFIED BECAUSE IT
WOULD CHANGE THE PAGE SIZE OF THE TABLE SPACE

SQLCODE -670, Error: THE RECORD LENGTH OF THE TABLE EXCEEDS THE PAGE SIZE LIMIT

SQLCODE -669, Error: A TABLE IN A PARTITIONED TABLE SPACE CANNOT BE EXPLICITLY DROPPED

SQLCODE -668, Error: THE COLUMN CANNOT BE ADDED TO THE TABLE BECAUSE THE TABLE HAS AN EDIT PROCEDURE

SQLCODE -667, Error: THE CLUSTERING INDEX FOR A PARTITIONED TABLE SPACE CANNOT BE EXPLICITLY DROPPED

SQLCODE -666, Error: CANNOT BE EXECUTED BECAUSE IS IN PROGRESS

SQLCODE -665, Error: THE PART CLAUSE OF AN ALTER STATEMENT IS OMITTED OR INVALID

SQLCODE -663, Error: THE NUMBER OF KEY LIMIT VALUES IS EITHER ZERO, OR GREATER THAN THE NUMBER OF COLUMNS IN THE KEY OF INDEX

SQLCODE -662, Error: A PARTITIONED INDEX CANNOT BE CREATED ON A NON-PARTITIONED TABLE SPACE

SQLCODE -661, Error: INDEX CANNOT BE CREATED ON PARTITIONED TABLE SPACE BECAUSE THE NUMBER OF PART
SPECIFICATIONS IS NOT EQUAL TO THE NUMBER OF PARTITIONS OF THE TABLE SPACE

SQLCODE -660, Error: INDEX CANNOT BE CREATED ON PARTITIONED TABLE SPACE x BECAUSE KEY LIMITS ARE NOT SPECIFIED

SQLCODE -658, Error: A CANNOT BE DROPPED USING THE STATEMENT

SQLCODE -655, Error: THE CREATE OR ALTER STOGROUP IS INVALID BECAUSE THE STORAGE GROUP WOULD HAVE BOTH
SPECIFIC AND NON-SPECIFIC VOLUME IDS

SQLCODE -653, Error: TABLE IN PARTITIONED TABLE SPACE IS NOT AVAILABLE BECAUSE ITS PARTITIONED INDEX HAS NOT BEEN CREATED

SQLCODE -652, Error: VIOLATION OF INSTALLATION DEFINED EDIT OR VALIDATION PROCEDURE

SQLCODE -651, Error: TABLE DESCRIPTION EXCEEDS MAXIMUM SIZE OF OBJECT DESCRIPTOR

SQLCODE -650, Error: THE ALTER INDEX CANNOT BE EXECUTED, REASON

SQLCODE -647, Error: BUFFERPOOL CANNOT BE SPECIFIED BECAUSE IT HAS NOT BEEN ACTIVATED

SQLCODE -646, Error: TABLE CANNOT BE CREATED IN SPECIFIED TABLE SPACE BECAUSE IT ALREADY CONTAINS A TABLE

SQLCODE -644, Error: INVALID VALUE SPECIFIED FOR KEYWORD IN STATEMENT

SQLCODE -643, Error: CHECK CONSTRAINT EXCEEDS MAXIMUM ALLOWABLE LENGTH

SQLCODE -642, Error: TOO MANY COLUMNS IN UNIQUE CONSTRAINTS

SQLCODE -640, Error: LOCKSIZE ROW CANNOT BE SPECIFIED BECAUSE TABLE IN THIS TABLESPACE HAS TYPE 1 INDEX

SQLCODE -639, Error: 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

SQLCODE -638, Error: TABLE CANNOT BE CREATED BECAUSE COLUMN DEFINITION IS MISSING

SQLCODE -637, Error: DUPLICATE KEYWORD

SQLCODE -636, Error: THE PARTITIONING KEYS FOR PARTITION ARE NOT SPECIFIED IN ASCENDING OR DESCENDING ORDER

SQLCODE -635, Error: THE DELETE RULES CANNOT BE DIFFERENT OR CANNOT BE SET NULL

SQLCODE -634, Error: THE DELETE RULE MUST NOT BE CASCADE

SQLCODE -633, Error: THE DELETE RULE MUST BE

SQLCODE -632, Error: THE TABLE CANNOT BE DEFINED AS A DEPENDENT OF BECAUSE OF DELETE RULE RESTRICTIONS

SQLCODE -631, Error: FOREIGN KEY IS TOO LONG OR HAS TOO MANY COLUMNS

SQLCODE -630, Error: THE WHERE NOT NULL SPECIFICATION IS INVALID FOR TYPE 1 INDEXES

SQLCODE -629, Error: SET NULL CANNOT BE SPECIFIED BECAUSE FOREIGN KEY CANNOT CONTAIN NULL VALUES

SQLCODE -628, Error: THE CLAUSES ARE MUTUALLY EXCLUSIVE

SQLCODE -627, Error: THE ALTER STATEMENT IS INVALID BECAUSE THE PAGESET HAS USER-MANAGED DATA SETS

SQLCODE -626, Error: THE ALTER STATEMENT IS NOT EXECUTABLE BECAUSE THE PAGE SET IS NOT STOPPED

SQLCODE -625, Error: TABLE DOES NOT HAVE AN INDEX TO ENFORCE THE UNIQUENESS OF THE PARENT KEY

SQLCODE -624, Error: TABLE ALREADY HAS A PRIMARY KEY

SQLCODE -623, Error: A CLUSTERING INDEX ALREADY EXISTS ON TABLE

SQLCODE -622, Error: FOR MIXED DATA IS INVALID BECAUSE THE MIXED DATA INSTALL OPTION IS NO

SQLCODE -621, Error: DUPLICATE DBID WAS DETECTED AND PREVIOUSLY ASSIGNED TO

SQLCODE -620, Error: KEYWORD IN STATEMENT IS NOT PERMITTED FOR A SPACE IN THE DATABASE

SQLCODE -619, Error: OPERATION DISALLOWED BECAUSE THE WORK FILE DATABASE IS NOT STOPPED

SQLCODE -618, Error: OPERATION IS NOT ALLOWED ON SYSTEM DATABASES

SQLCODE -617, Error: A TYPE 1 INDEX IS NOT VALID FOR TABLE

SQLCODE -616, Error: CANNOT BE DROPPED BECAUSE IT IS REFERENCED BY

SQLCODE -615, Error: IS NOT ALLOWED ON A PACKAGE IN USE

SQLCODE -614, Error: THE INDEX CANNOT BE CREATED OR THE LENGTH OF THE COLUMN CANNOT BE CHANGED BECAUSE THE
SUM OF THE INTERNAL LENGTHS OF THE IDENTIFIED COLUMNS IS GREATER THAN THE ALLOWABLE MAXIMUM

SQLCODE -613, Error: THE PRIMARY KEY OR A UNIQUE CONSTRAINT IS TOO LONG OR HAS TOO MANY COLUMNS

SQLCODE -612, Error: IS A DUPLICATE COLUMN NAME

SQLCODE -611, Error: ONLY LOCKMAX 0 CAN BE SPECIFIED WHEN THE LOCK SIZE OF THE TABLESPACE IS TABLESPACE OR TABLE

SQLCODE -607, Error: OPERATION OR OPTION IS NOT DEFINED FOR THIS OBJECT

SQLCODE -604, Error: A DATA TYPE DEFINITION SPECIFIES AN INVALID LENGTH, PRECISION, OR SCALE ATTRIBUTE

SQLCODE -603, Error: A UNIQUE INDEX CANNOT BE CREATED BECAUSE THE TABLE CONTAINS ROWS WHICH ARE DUPLICATES
WITH RESPECT TO THE VALUES OF THE IDENTIFIED COLUMNS

SQLCODE -602, Error: TOO MANY COLUMNS SPECIFIED IN A CREATE INDEX

SQLCODE -601, Error: THE NAME OF THE OBJECT TO BE CREATED OR THE TARGET OF A RENAME STATEMENT IS IDENTICAL
TO THE EXISTING NAME OF THE OBJECT TYPE

SQLCODE -594, Error: ATTEMPT TO CREATE A NULLABLE ROWID OR DISTINCT TYPE COLUMN

SQLCODE -593, Error: NOT NULL MUST BE SPECIFIED FOR ROWID OR DISTINCT TYPE COLUMN

SQLCODE -592, Error: NOT AUTHORIZED TO CREATE FUNCTIONS OR PROCEDURES IN WLM ENVIRONMENT

SQLCODE -590, Error: PARAMETER NAME IS NOT UNIQUE IN THE CREATE FOR ROUTINE

SQLCODE -586, Error: THE TOTAL LENGTH OF THE CURRENT PATH SPECIAL REGISTER CANNOT EXCEED 254 CHARACTERS

SQLCODE -585, Error: THE SCHEMA NAME CANNOT APPEAR MORE THAN ONCE IN THE CURRENT PATH

SQLCODE -582, Error: THE SEARCH-CONDITION IN A SEARCHED-WHEN-CLAUSE CANNOT BE A QUANTIFIED PREDICATE, IN
PREDICATE, OR AN EXISTS PREDICATE

SQLCODE -581, Error: THE DATA TYPES OF THE RESULT-EXPRESSIONS OF A CASE EXPRESSION ARE NOT COMPATIBLE

SQLCODE -580, Error: THE RESULT-EXPRESSIONS OF A CASE EXPRESSION CANNOT ALL BE NULL

SQLCODE -579, Error: ATTEMPTED TO READ DATA WHEN THE DEFINITION OF THE FUNCTION OR PROCEDURE DID NOT SPECIFY THIS ACTION

SQLCODE -577, Error: ATTEMPTED TO MODIFY DATA WHEN THE DEFINITION OF THE FUNCTION OR PROCEDURE DID NOT SPECIFY THIS ACTION

SQLCODE -574, Error: THE SPECIFIED DEFAULT VALUE OR IDENTITY ATTRIBUTE VALUE CONFLICTS WITH THE DEFINITION OF COLUMN

SQLCODE -573, Error: TABLE DOES NOT HAVE A UNIQUE KEY WITH THE SPECIFIED COLUMN NAMES

SQLCODE -571, Error: THE STATEMENT WOULD RESULT IN A MULTIPLE SITE UPDATE

SQLCODE -567, Error: AUTHORIZATION ERROR USING AUTHORITY PACKAGE = PRIVILEGE =

SQLCODE -559, Error: ALL AUTHORIZATION FUNCTIONS HAVE BEEN DISABLED

SQLCODE -558, Error: INVALID CLAUSE OR COMBINATION OF CLAUSES ON A GRANT OR REVOKE

SQLCODE -557, Error: INCONSISTENT GRANT/REVOKE KEYWORD . PERMITTED KEYWORDS ARE

SQLCODE -556, Error: CANNOT HAVE THE PRIVILEGE REVOKED BY BECAUSE THE REVOKEE DOES NOT POSSESS THE
PRIVILEGE OR THE REVOKER DID NOT MAKE THE GRANT

SQLCODE -555, Error: AN AUTHORIZATION ID CANNOT REVOKE A PRIVILEGE FROM ITSELF

SQLCODE -554, Error: AN AUTHORIZATION ID CANNOT GRANT A PRIVILEGE TO ITSELF

SQLCODE -553, Error: SPECIFIED IS NOT ONE OF THE VALID AUTHORIZATION IDS

SQLCODE -552, Error: DOES NOT HAVE THE PRIVILEGE TO PERFORM OPERATION

SQLCODE -551, Error: DOES NOT HAVE THE PRIVILEGE TO PERFORM OPERATION ON OBJECT

SQLCODE -549, Error: THE STATEMENT IS NOT ALLOWED FOR BECAUSE THE BIND OPTION DYNAMICRULES(RUN) IS NOT IN EFFECT FOR

SQLCODE -548, Error: A CHECK CONSTRAINT THAT IS DEFINED WITH IS INVALID

SQLCODE -546, Error: THE CHECK CONSTRAINT IS INVALID

SQLCODE -545, Error: THE REQUESTED OPERATION IS NOT ALLOWED BECAUSE A ROW DOES NOT SATISFY THE CHECK CONSTRAINT

SQLCODE -544, Error: THE CHECK CONSTRAINT SPECIFIED IN THE ALTER TABLE STATEMENT CANNOT BE ADDED BECAUSE AN
EXISTING ROW VIOLATES THE CHECK CONSTRAINT

SQLCODE -543, Error: A ROW IN A PARENT TABLE CANNOT BE DELETED BECAUSE THE CHECK CONSTRAINT RESTRICTS THE DELETION

SQLCODE -542, Error: CANNOT BE A COLUMN OF A PRIMARY KEY, A UNIQUE CONSTRAINT, OR A PARENT KEY BECAUSE IT
CAN CONTAIN NULL VALUES

SQLCODE -540, Error: THE DEFINITION OF TABLE IS INCOMPLETE BECAUSE IT LACKS A PRIMARY INDEX OR A REQUIRED UNIQUE INDEX

SQLCODE -539, Error: TABLE DOES NOT HAVE A PRIMARY KEY

SQLCODE -538, Error: FOREIGN KEY DOES NOT CONFORM TO THE DESCRIPTION OF A PARENT KEY OF TABLE

SQLCODE -537, Error: THE PRIMARY KEY CLAUSE, A FOREIGN KEY CLAUSE, OR A UNIQUE CLAUSE IDENTIFIES COLUMN MORE THAN ONCE

SQLCODE -536, Error: THE DELETE STATEMENT IS INVALID BECAUSE TABLE CAN BE AFFECTED BY THE OPERATION

SQLCODE -534, Error: THE PRIMARY KEY CANNOT BE UPDATED BECAUSE OF MULTIPLE-ROW UPDATE

SQLCODE -533, Error: INVALID MULTIPLE-ROW INSERT

SQLCODE -532, Error: THE RELATIONSHIP RESTRICTS THE DELETION OF ROW WITH RID X''

SQLCODE -531, Error: PARENT KEY IN A PARENT ROW CANNOT BE UPDATED BECAUSE IT HAS ONE OR MORE DEPENDENT ROWS IN RELATIONSHIP

SQLCODE -530, Error: THE INSERT OR UPDATE VALUE OF FOREIGN KEY IS INVALID

SQLCODE -526, Error: THE REQUESTED OPERATION OR USAGE DOES NOT APPLY TO TEMPORARY TABLE

SQLCODE -525, Error: THE SQL STATEMENT CANNOT BE EXECUTED BECAUSE IT WAS IN ERROR AT BIND TIME FOR SECTION
= PACKAGE = CONSISTENCY TOKEN = X''

SQLCODE -519, Error: THE PREPARE STATEMENT IDENTIFIES THE SELECT STATEMENT OF THE OPENED CURSOR

SQLCODE -518, Error: THE EXECUTE STATEMENT DOES NOT IDENTIFY A VALID PREPARED STATEMENT

SQLCODE -517, Error: CURSOR CANNOT BE USED BECAUSE ITS STATEMENT NAME DOES NOT IDENTIFY A PREPARED SELECT STATEMENT

SQLCODE -516, Error: THE DESCRIBE FOR STATIC STATEMENT DOES NOT IDENTIFY A PREPARED STATEMENT

SQLCODE -514, Error: THE CURSOR IS NOT IN A PREPARED STATE

SQLCODE -513, Error: THE ALIAS MUST NOT BE DEFINED ON ANOTHER LOCAL OR REMOTE ALIAS

SQLCODE -512, Error: STATEMENT REFERENCE TO REMOTE OBJECT IS INVALID

SQLCODE -511, Error: THE FOR UPDATE CLAUSE CANNOT BE SPECIFIED BECAUSE THE TABLE DESIGNATED BY THE CURSOR CANNOT BE MODIFIED

SQLCODE -510, Error: THE TABLE DESIGNATED BY THE CURSOR OF THE UPDATE OR DELETE STATEMENT CANNOT BE MODIFIED

SQLCODE -509, Error: THE TABLE IDENTIFIED IN THE UPDATE OR DELETE STATEMENT IS NOT THE SAME TABLE
DESIGNATED BY THE CURSOR

SQLCODE -508, Error: THE CURSOR IDENTIFIED IN THE UPDATE OR DELETE STATEMENT IS NOT POSITIONED ON A ROW

SQLCODE -507, Error: THE CURSOR IDENTIFIED IN THE UPDATE OR DELETE STATEMENT IS NOT OPEN

SQLCODE -504, Error: THE CURSOR NAME IS NOT DEFINED

SQLCODE -503, Error: A COLUMN CANNOT BE UPDATED BECAUSE IT IS NOT IDENTIFIED IN THE UPDATE CLAUSE OF THE
SELECT STATEMENT OF THE CURSOR

SQLCODE -502, Error: THE CURSOR IDENTIFIED IN AN OPEN STATEMENT IS ALREADY OPEN

SQLCODE -501, Error: THE CURSOR IDENTIFIED IN A FETCH OR CLOSE STATEMENT IS NOT OPEN

SQLCODE -500, Error: THE IDENTIFIED CURSOR WAS CLOSED WHEN THE CONNECTION WAS DESTROYED

SQLCODE -499, Error: CURSOR HAS ALREADY BEEN ASSIGNED TO THIS OR ANOTHER RESULT SET FROM PROCEDURE

SQLCODE -497, Error: THE MAXIMUM LIMIT OF INTERNAL IDENTIFIERS HAS BEEN EXCEEDED FOR DATABASE

SQLCODE -496, Error: THE SQL STATEMENT CANNOT BE EXECUTED BECAUSE IT REFERENCES A RESULT SET THAT WAS NOT
CREATED BY THE CURRENT SERVER

SQLCODE -495, Error: ESTIMATED PROCESSOR COST OF PROCESSOR SECONDS ( SERVICE UNITS) IN COST CATEGORY
EXCEEDS A RESOURCE LIMIT ERROR THRESHOLD OF SERVICE UNITS

SQLCODE -492, Error: THE CREATE FUNCTION FOR HAS A PROBLEM WITH PARAMETER NUMBER . IT MAY INVOLVE A
MISMATCH WITH A SOURCE FUNCTION

SQLCODE -491, Error: CREATE STATEMENT FOR USER-DEFINED FUNCTION MUST HAVE A RETURNS CLAUSE, AND EITHER THE
EXTERNAL CLAUSE (WITH OTHER REQUIRED KEYWORDS) OR THE SOURCE CLAUSE

SQLCODE -490, Error: NUMBER DIRECTLY SPECIFIED IN AN SQL STATEMENT IS OUTSIDE THE RANGE OF ALLOWABLE
VALUES IN THIS CONTEXT (, )

SQLCODE -487, Error: ATTEMPTED TO EXECUTE AN SQL STATEMENT WHEN THE DEFINITION OF THE FUNCTION OR
PROCEDURE DID NOT SPECIFY THIS ACTION

SQLCODE -483, Error: IN CREATE FUNCTION FOR STATEMENT, THE NUMBER OF PARAMETERS DOES NOT MATCH THE NUMBER
OF PARAMETERS OF THE SOURCE FUNCTION

SQLCODE -482, Error: THE PROCEDURE RETURNED NO LOCATORS

SQLCODE -480, Error: THE PROCEDURE HAS NOT YET BEEN CALLED

SQLCODE -478, Error: DROP OR REVOKE ON OBJECT TYPE CANNOT BE PROCESSED BECAUSE OBJECT OF TYPE IS
DEPENDENT ON IT

SQLCODE -476, Error: REFERENCE TO FUNCTION WAS NAMED WITHOUT A SIGNATURE, BUT THE FUNCTION IS NOT UNIQUE
WITHIN ITS SCHEMA

SQLCODE -475, Error: THE RESULT TYPE OF THE SOURCE FUNCTION CANNOT BE CAST TO THE RETURNS TYPE OF THE
USER-DEFINED FUNCTION

SQLCODE -473, Error: A USER DEFINED DATA TYPE CANNOT BE CALLED THE SAME NAME AS A SYSTEM PREDEFINED TYPE
(BUILT-IN TYPE)

SQLCODE -472, Error: CURSOR WAS LEFT OPEN BY EXTERNAL FUNCTION (SPECIFIC NAME )

SQLCODE -471, Error: INVOCATION OF FUNCTION OR PROCEDURE FAILED DUE TO REASON

SQLCODE -470, Error: SQL CALL STATEMENT SPECIFIED A NULL VALUE FOR INPUT PARAMETER , BUT THE STORED
PROCEDURE DOES NOT SUPPORT NULL VALUES

SQLCODE -469, Error: SQL CALL STATEMENT MUST SPECIFY AN OUTPUT HOST VARIABLE FOR PARAMETER

SQLCODE -463, Error: EXTERNAL FUNCTION (SPECIFIC NAME ) HAS RETURNED AN INVALID SQLSTATE , WITH DIAGNOSTIC TEXT

SQLCODE -461, Error: A VALUE WITH DATA TYPE CANNOT BE CAST TO TYPE

SQLCODE -458, Error: IN A REFERENCE TO FUNCTION BY SIGNATURE, A MATCHING FUNCTION COULD NOT BE FOUND

SQLCODE -457, Error: A FUNCTION OR DISTINCT TYPE CANNOT BE CALLED SINCE IT IS RESERVED FOR SYSTEM USE

SQLCODE -456, Error: IN CREATE FUNCTION FOR , THE SPECIFIC NAME ALREADY EXISTS IN THE SCHEMA

SQLCODE -455, Error: IN CREATE FUNCTION FOR , THE SCHEMA NAME PROVIDED FOR THE SPECIFIC NAME DOES NOT
MATCH THE SCHEMA NAME OF THE FUNCTION

SQLCODE -454, Error: THE SIGNATURE PROVIDED IN THE CREATE FUNCTION STATEMENT FOR MATCHES THE SIGNATURE OF
SOME OTHER FUNCTION ALREADY EXISTING IN THE SCHEMA

SQLCODE -453, Error: THERE IS A PROBLEM WITH THE RETURNS CLAUSE IN THE CREATE FUNCTION STATEMENT FOR

SQLCODE -451, Error: THE DEFINITION, IN THE CREATE FUNCTION FOR CONTAINS DATA TYPE WHICH IS NOT
APPROPRIATE FOR AN EXTERNAL FUNCTION WRITTEN IN THE GIVEN LANGUAGE

SQLCODE -450, Error: USER-DEFINED FUNCTION OR STORED PROCEDURE , PARAMETER NUMBER , OVERLAYED STORAGE
BEYOND ITS DECLARED LENGTH

SQLCODE -449, Error: CREATE OR ALTER STATEMENT FOR FUNCTION OR PROCEDURE CONTAINS AN INVALID FORMAT OF THE
EXTERNAL NAME CLAUSE OR IS MISSING THE EXTERNAL NAME CLAUSE

SQLCODE -444, Error: USER PROGRAM COULD NOT BE FOUND

SQLCODE -443, Error: EXTERNAL FUNCTION (SPECIFIC NAME ) HAS RETURNED AN ERROR SQLSTATE WITH DIAGNOSTIC TEXT

SQLCODE -441, Error: INVALID USE OF 'DISTINCT' OR 'ALL' WITH SCALAR FUNCTION

SQLCODE -440, Error: NO BY THE NAME HAVING COMPATIBLE ARGUMENTS WAS FOUND IN THE CURRENT PATH

SQLCODE -438, Error: APPLICATION RAISED ERROR WITH DIAGNOSTIC TEXT:

SQLCODE -435, Error: AN INVALID SQLSTATE IS SPECIFIED IN THE FUNCTION RAISE_ERROR OR IN A SIGNAL SQLSTATE STATEMENT

SQLCODE -433, Error: VALUE IS TOO LONG

SQLCODE -430, ERROR: (SPECIFIC NAME ) HAS ABNORMALLY TERMINATED

SQLCODE -427, Error: DYNAMIC ROLLBACK NOT VALID AT AN APPLICATION SERVER WHERE UPDATES ARE NOT ALLOWED

SQLCODE -426, Error: DYNAMIC COMMIT NOT VALID AT AN APPLICATION SERVER WHERE UPDATES ARE NOT ALLOWED

SQLCODE -423, Error: INVALID VALUE FOR LOCATOR IN POSITION

SQLCODE -421, Error: THE OPERANDS OF A UNION OR UNION ALL DO NOT HAVE THE SAME NUMBER OF COLUMNS

SQLCODE -420, Error: THE VALUE OF A CHARACTER STRING ARGUMENT WAS NOT ACCEPTABLE TO THE FUNCTION

SQLCODE -419, Error: A DECIMAL DIVIDE OPERATION IS INVALID BECAUSE THE RESULT WOULD HAVE A NEGATIVE SCALE

SQLCODE -418, Error: A STATEMENT STRING TO BE PREPARED CONTAINS AN INVALID USE OF PARAMETER MARKERS

SQLCODE -417, Error: A STATEMENT STRING TO BE PREPARED INCLUDES PARAMETER MARKERS AS THE OPERANDS OF THE SAME OPERATOR

SQLCODE -416, Error: AN OPERAND OF A UNION CONTAINS A LONG STRING COLUMN

SQLCODE -415, Error: THE CORRESPONDING COLUMNS, , OF THE OPERANDS OF A UNION OR A UNION ALL DO NOT HAVE
COMPARABLE COLUMN DESCRIPTIONS

SQLCODE -414, Error: A LIKE PREDICATE IS INVALID BECAUSE THE FIRST OPERAND IS NOT A STRING

SQLCODE -413, Error: OVERFLOW OCCURRED DURING NUMERIC DATA TYPE CONVERSION

SQLCODE -412, Error: THE SELECT CLAUSE OF A SUBQUERY SPECIFIES MULTIPLE COLUMNS

SQLCODE -411, Error: CURRENT SQLID CANNOT BE USED IN A STATEMENT THAT REFERENCES REMOTE OBJECTS

SQLCODE -410, Error: THE FLOATING POINT LITERAL CONTAINS MORE THAN 30 CHARACTERS

SQLCODE -409, Error: INVALID OPERAND OF A COUNT FUNCTION

SQLCODE -408, Error: THE VALUE IS NOT COMPATIBLE WITH THE DATA TYPE OF ITS TARGET

SQLCODE -407, Error: AN UPDATE, INSERT, OR SET VALUE IS NULL, BUT THE OBJECT COLUMN CANNOT CONTAIN NULL VALUES

SQLCODE -406, Error: A CALCULATED OR DERIVED NUMERIC VALUE IS NOT WITHIN THE RANGE OF ITS OBJECT COLUMN

SQLCODE -405, Error: THE NUMERIC LITERAL CANNOT BE USED AS SPECIFIED BECAUSE IT IS OUT OF RANGE

SQLCODE -404, Error: THE SQL STATEMENT SPECIFIES A STRING THAT IS TOO LONG

SQLCODE -402, Error: AN ARITHMETIC FUNCTION OR OPERATOR IS APPLIED TO CHARACTER OR DATETIME DATA

SQLCODE -401, Error: THE OPERANDS OF AN ARITHMETIC OR COMPARISON OPERATION ARE NOT COMPARABLE

SQLCODE -400, Error: THE CATALOG HAS THE MAXIMUM NUMBER OF USER DEFINED INDEXES

SQLCODE -399, Error: ATTEMPTED TO INSERT AN INVALID VALUE INTO A ROWID COLUMN

SQLCODE -398, Error: A LOCATOR WAS REQUESTED FOR HOST VARIABLE NUMBER BUT THE VARIABLE IS NOT A LOB

SQLCODE -397, Error: THE OPTION GENERATED IS SPECIFIED WITH A COLUMN THAT IS NOT A ROW ID OR DISTINCT TYPE BASED ON A ROW ID

SQLCODE -396, Error: ATTEMPTED TO EXECUTE AN SQL STATEMENT DURING FINAL CALL PROCESSING

SQLCODE -392, Error: SQLDA PROVIDED FOR CURSOR HAS BEEN CHANGED FROM THE PREVIOUS FETCH

SQLCODE -390, Error: THE FUNCTION NAME , SPECIFIC NAME , IS NOT VALID IN THE CONTEXT IN WHICH IT OCCURS

SQLCODE -373, Error: DEFAULT CANNOT BE SPECIFIED FOR IDENTITY COLUMN

SQLCODE -372, Error: ONLY ONE ROWID OR IDENTITY COLUMN IS ALLOWED IN A TABLE

SQLCODE -359, Error: THE RANGE OF VALUES FOR THE IDENTITY COLUMN IS EXHAUSTED

SQLCODE -355, Error: A LOB COLUMN IS TOO LARGE TO BE LOGGED

SQLCODE -352, Error: AN UNSUPPORTED SQLTYPE WAS ENCOUNTERED IN POSITION OF THE INPUT-LIST

SQLCODE -351, Error: AN UNSUPPORTED SQLTYPE WAS ENCOUNTERED IN POSITION OF THE SELECT-LIST

SQLCODE -350, Error: INVALID SPECIFICATION OF A LARGE OBJECT COLUMN

SQLCODE -339, Error: THE SQL STATEMENT CANNOT BE EXECUTED FROM AN ASCII BASED DRDA APPLICATION REQUESTOR TO
A V2R2 DB2 SUBSYSTEM

SQLCODE -338, Error: AN ON CLAUSE IS INVALID

SQLCODE -333, Error: 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

SQLCODE -332, Error: SYSSTRINGS DOES NOT DEFINE A TRANSLATION FROM CCSID TO

SQLCODE -331, Error: A STRING CANNOT BE ASSIGNED TO A HOST VARIABLE BECAUSE IT CANNOT BE TRANSLATED.
REASON , CHARACTER , POSITION

SQLCODE -330, Error: A STRING CANNOT BE USED BECAUSE IT CANNOT BE TRANSLATED. REASON , CHARACTER , HOST VARIABLE

SQLCODE -327, Error: THE ROW CANNOT BE INSERTED BECAUSE IT IS OUTSIDE THE BOUND OF THE PARTITION RANGE FOR
THE LAST PARTITION

SQLCODE -314, Error: THE STATEMENT CONTAINS AN AMBIGUOUS HOST VARIABLE REFERENCE

SQLCODE -313, Error: THE NUMBER OF HOST VARIABLES SPECIFIED IS NOT EQUAL TO THE NUMBER OF PARAMETER MARKERS

SQLCODE -312, Error: IS AN UNDEFINED OR UNUSABLE HOST VARIABLE OR IS USED IN A DYNAMIC SQL STATEMENT OR A
TRIGGER DEFINITION

SQLCODE -311, Error: THE LENGTH OF INPUT HOST VARIABLE NUMBER IS NEGATIVE OR GREATER THAN THE MAXIMUM

SQLCODE -310, Error: DECIMAL HOST VARIABLE OR PARAMETER CONTAINS NON-DECIMAL DATA

SQLCODE -309, Error: A PREDICATE IS INVALID BECAUSE A REFERENCED HOST VARIABLE HAS THE NULL VALUE

SQLCODE -305, Error: THE NULL VALUE CANNOT BE ASSIGNED TO OUTPUT HOST VARIABLE NUMBER BECAUSE NO INDICATOR
VARIABLE IS SPECIFIED

SQLCODE -304, Error: A VALUE WITH DATA TYPE CANNOT BE ASSIGNED TO A HOST VARIABLE BECAUSE THE VALUE IS NOT
WITHIN THE RANGE OF THE HOST VARIABLE IN POSITION WITH DATA TYPE

SQLCODE -303, Error: A VALUE CANNOT BE ASSIGNED TO OUTPUT HOST VARIABLE NUMBER BECAUSE THE DATA TYPES ARE
NOT COMPARABLE

SQLCODE -302, Error: THE VALUE OF INPUT VARIABLE OR PARAMETER NUMBER IS INVALID OR TOO LARGE FOR THE
TARGET COLUMN OR THE TARGET VALUE

SQLCODE -301, Error: THE VALUE OF INPUT HOST VARIABLE OR PARAMETER NUMBER CANNOT BE USED AS SPECIFIED
BECAUSE OF ITS DATA TYPE

SQLCODE -300, Error: THE STRING CONTAINED IN HOST VARIABLE OR PARAMETER IS NOT NUL-TERMINATED

SQLCODE -251, Error: TOKEN IS NOT VALID

SQLCODE -250, Error: THE LOCAL LOCATION NAME IS NOT DEFINED WHEN PROCESSING A THREE-PART OBJECT NAME

SQLCODE -240, Error: THE PART CLAUSE OF A LOCK TABLE STATEMENT IS INVALID

SQLCODE -229, Error: THE LOCALE SPECIFIED IN A SET LOCALE OR OTHER STATEMENT THAT IS LOCALE SENSITIVE WAS NOT FOUND

SQLCODE -221, Error: "SET OF OPTIONAL COLUMNS" IN EXPLANATION TABLE IS INCOMPLETE. OPTIONAL COLUMN IS MISSING

SQLCODE -220, Error: THE COLUMN IN EXPLANATION TABLE IS NOT DEFINED PROPERLY

SQLCODE -219, Error: THE REQUIRED EXPLANATION TABLE DOES NOT EXIST

SQLCODE -214, Error: AN EXPRESSION STARTING WITH IN THE CLAUSE IS NOT VALID. REASON CODE =

SQLCODE -212, Error: IS SPECIFIED MORE THAN ONCE IN THE REFERENCING CLAUSE OF A TRIGGER DEFINITION

SQLCODE -208, Error: THE ORDER BY CLAUSE IS INVALID BECAUSE COLUMN IS NOT PART OF THE RESULT TABLE

SQLCODE -206, Error: IS NOT A COLUMN OF AN INSERTED TABLE, UPDATED TABLE, OR ANY TABLE IDENTIFIED IN A
FROM CLAUSE, OR IS NOT A COLUMN OF THE TRIGGERING TABLE OF A TRIGGER

SQLCODE -205, Error: IS NOT A COLUMN OF TABLE

SQLCODE -204, Error: IS AN UNDEFINED NAME

SQLCODE -203, Error: A REFERENCE TO COLUMN IS AMBIGUOUS

SQLCODE -199, Error: ILLEGAL USE OF KEYWORD , TOKEN WAS EXPECTED

SQLCODE -198, Error: THE OPERAND OF THE PREPARE OR EXECUTE IMMEDIATE STATEMENT IS BLANK OR EMPTY

SQLCODE -197, Error: QUALIFIED COLUMN NAMES IN ORDER BY CLAUSE NOT PERMITTED WHEN UNION OR UNION ALL
SPECIFIED

SQLCODE -191, Error: A STRING CANNOT BE USED BECAUSE IT IS INVALID MIXED DATA

SQLCODE -190, Error: ATTRIBUTES OF COLUMN IN TABLE ARE NOT COMPATIBLE WITH THE EXISTING COLUMN

SQLCODE -189, Error: CCSID IS UNKNOWN OR INVALID FOR THE DATA TYPE OR SUBTYPE

SQLCODE -188, Error: THE STRING REPRESENTATION OF A NAME IS INVALID

SQLCODE -187, Error: 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

SQLCODE -186, Error: THE LOCAL DATE LENGTH OR LOCAL TIME LENGTH HAS BEEN INCREASED AND EXECUTING PROGRAM
RELIES ON THE OLD LENGTH

SQLCODE -185, Error: THE LOCAL FORMAT OPTION HAS BEEN USED WITH A DATE OR TIME AND NO LOCAL EXIT HAS BEEN INSTALLED

SQLCODE -184, Error: AN ARITHMETIC EXPRESSION WITH A DATETIME VALUE CONTAINS A PARAMETER MARKER

SQLCODE -183, Error: AN ARITHMETIC OPERATION ON A DATE OR TIMESTAMP HAS A RESULT THAT IS NOT WITHIN THE
VALID RANGE OF DATES

SQLCODE -182, Error: AN ARITHMETIC EXPRESSION WITH A DATETIME VALUE IS INVALID

SQLCODE -181, Error: THE STRING REPRESENTATION OF A DATETIME VALUE IS NOT A VALID DATETIME VALUE

SQLCODE -180, Error: THE DATE, TIME, OR TIMESTAMP VALUE IS INVALID

SQLCODE -173, Error: UR IS SPECIFIED ON THE WITH CLAUSE BUT THE CURSOR IS NOT READ-ONLY

SQLCODE -171, Error: THE DATA TYPE, LENGTH, OR VALUE OF ARGUMENT OF IS INVALID

SQLCODE -170, Error: THE NUMBER OF ARGUMENTS SPECIFIED FOR IS INVALID

SQLCODE -164, Error: DOES NOT HAVE THE PRIVILEGE TO CREATE A VIEW WITH QUALIFICATION

SQLCODE -161, Error: THE INSERT OR UPDATE IS NOT ALLOWED BECAUSE A RESULTING ROW DOES NOT SATISFY THE VIEW DEFINITION

SQLCODE -160, Error: THE WITH CHECK OPTION CANNOT BE USED FOR THE SPECIFIED VIEW

SQLCODE -159, Error: DROP OR COMMENT ON IDENTIFIES A(N) RATHER THAN A(N)

SQLCODE -158, Error: THE NUMBER OF COLUMNS SPECIFIED FOR THE VIEW IS NOT THE SAME AS THE NUMBER OF COLUMNS
SPECIFIED BY THE SELECT CLAUSE,
OR THE NUMBER OF COLUMNS SPECIFIED IN THE CORRELATION CLAUSE IN A FROM CLAUSE IS NOT THE SAME AS THE NUMBER OF
COLUMNS IN THE CORRESPONDING TABLE, VIEW, TABLE EXPRESSION, OR TABLE FUNCTION

SQLCODE -157, Error: ONLY A TABLE NAME CAN BE SPECIFIED IN A FOREIGN KEY CLAUSE. IS NOT THE NAME OF A TABLE

SQLCODE -156, Error: THE STATEMENT DOES NOT IDENTIFY A TABLE

SQLCODE -154, Error: THE STATEMENT IS INVALID BECAUSE THE VIEW OR TABLE DEFINITION IS NOT VALID

SQLCODE -153, Error: THE STATEMENT IS INVALID BECAUSE THE VIEW OR TABLE DEFINITION DOES NOT INCLUDE A
UNIQUE NAME FOR EACH COLUMN

SQLCODE -152, Error: THE DROP CLAUSE IN THE ALTER STATEMENT IS INVALID BECAUSE IS A

SQLCODE -151, Error: THE UPDATE STATEMENT IS INVALID BECAUSE THE CATALOG DESCRIPTION OF COLUMN INDICATES
THAT IT CANNOT BE UPDATED

SQLCODE -150, Error: THE OBJECT OF THE INSERT, DELETE, OR UPDATE STATEMENT IS A VIEW OR TRANSITION TABLE
FOR WHICH THE REQUESTED OPERATION IS NOT PERMITTED

SQLCODE -148, Error: THE SOURCE TABLE CANNOT BE RENAMED OR ALTERED

SQLCODE -147, Error: ALTER FUNCTION FAILED BECAUSE SOURCE FUNCTIONS CANNOT BE ALTERED

SQLCODE -144, Error: INVALID SECTION NUMBER

SQLCODE -142, Error: THE SQL STATEMENT IS NOT SUPPORTED

SQLCODE -138, Error: THE SECOND OR THIRD ARGUMENT OF THE SUBSTR FUNCTION IS OUT OF RANGE

SQLCODE -137, Error: THE LENGTH RESULTING FROM IS GREATER THAN

SQLCODE -136, Error: SORT CANNOT BE EXECUTED BECAUSE THE SORT KEY LENGTH IS GREATER THAN 4000 BYTES

SQLCODE -134, Error: IMPROPER USE OF LONG STRING COLUMN OR AN EXPRESSION OF MAXIMUM LENGTH GREATER THAN 255

SQLCODE -133, Error: 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

SQLCODE -132, Error: AN OPERAND OF x IS NOT VALID

SQLCODE -131, Error: STATEMENT WITH LIKE PREDICATE HAS INCOMPATIBLE DATA TYPES

SQLCODE -130, Error: THE ESCAPE CLAUSE CONSISTS OF MORE THAN ONE CHARACTER, OR THE STRING PATTERN CONTAINS
AN INVALID OCCURRENCE OF THE ESCAPE CHARACTER

SQLCODE -129, Error: THE STATEMENT CONTAINS TOO MANY TABLE NAMES

SQLCODE -128, Error: INVALID USE OF NULL IN A PREDICATE

SQLCODE -127, Error: DISTINCT IS SPECIFIED MORE THAN ONCE IN A SUBSELECT

SQLCODE -126, Error: THE SELECT STATEMENT CONTAINS BOTH AN UPDATE CLAUSE AND AN ORDER BY CLAUSE

SQLCODE -125, Error: AN INTEGER IN THE ORDER BY CLAUSE DOES NOT IDENTIFY A COLUMN OF THE RESULT

SQLCODE -123, Error: THE PARAMETER IN POSITION IN THE FUNCTION MUST BE A CONSTANT OR KEYWORD

SQLCODE -122, Error: 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

SQLCODE -121, Error: THE COLUMN IS IDENTIFIED MORE THAN ONCE IN THE INSERT OR UPDATE OR SET TRANSITION
VARIABLE STATEMENT

SQLCODE -120, Error: A WHERE CLAUSE, SET CLAUSE, VALUES CLAUSE, OR A SET ASSIGNMENT STATEMENT INCLUDES A
COLUMN FUNCTION

SQLCODE -119, Error: A COLUMN IDENTIFIED IN A HAVING CLAUSE IS NOT INCLUDED IN THE GROUP BY CLAUSE

SQLCODE -118, Error: THE OBJECT TABLE OR VIEW OF THE DELETE OR UPDATE STATEMENT IS ALSO IDENTIFIED IN A FROM CLAUSE

SQLCODE -117, Error: THE NUMBER OF ASSIGNED VALUES IS NOT THE SAME AS THE NUMBER OF SPECIFIED OR IMPLIED
COLUMNS

SQLCODE -115, Error: A PREDICATE IS INVALID BECAUSE THE COMPARISON OPERATOR IS FOLLOWED BY A PARENTHESIZED
LIST OR BY ANY OR ALL WITHOUT A SUBQUERY

SQLCODE -114, Error: THE LOCATION NAME DOES NOT MATCH THE CURRENT SERVER

SQLCODE -113, Error: INVALID CHARACTER FOUND IN: . REASON CODE

SQLCODE -112, Error: THE OPERAND OF A COLUMN FUNCTION IS ANOTHER COLUMN FUNCTION

SQLCODE -111, Error: A COLUMN FUNCTION DOES NOT INCLUDE A COLUMN NAME

SQLCODE -110, Error: INVALID HEXADECIMAL LITERAL BEGINNING

SQLCODE -109, Error: CLAUSE IS NOT PERMITTED

SQLCODE -108, Error: THE NAME IS QUALIFIED INCORRECTLY

SQLCODE -107, Error: THE NAME IS TOO LONG. MAXIMUM ALLOWABLE SIZE IS

SQLCODE -105, Error: INVALID STRING

SQLCODE -104, Error: ILLEGAL SYMBOL "". SOME SYMBOLS THAT MIGHT BE LEGAL ARE:

SQLCODE -103, Error: IS AN INVALID NUMERIC LITERAL

SQLCODE -102, Error: LITERAL STRING IS TOO LONG. STRING BEGINS

SQLCODE -101, Error: THE STATEMENT IS TOO LONG OR TOO COMPLEX

SQLCODE -097, Error: THE USE OF LONG VARCHAR OR LONG VARGRAPHIC IS NOT ALLOWED IN THIS CONTEXT

SQLCODE -084, Error: UNACCEPTABLE SQL STATEMENT

SQLCODE -079, Error: QUALIFIER FOR DECLARED GLOBAL TEMPORARY TABLE OR INDEX MUST BE SESSION, NOT

SQLCODE -060, Error: INVALID SPECIFICATION :

SQLCODE -029, Error: INTO CLAUSE REQUIRED

SQLCODE -010, Error: THE STRING CONSTANT BEGINNING IS NOT TERMINATED

SQLCODE -007, Error: STATEMENT CONTAINS THE ILLEGAL CHARACTER

SQLCODE 000,SUCCESSFUL EXECUTION

SQLCODE 012, Warning: THE UNQUALIFIED COLUMN NAME WAS INTERPRETED AS A CORRELATED REFERENCE

SQLCODE 098, Warning: A DYNAMIC SQL STATEMENT ENDS WITH A SEMICOLON

SQLCODE 100, NOT FOUND:ROW NOT FOUND FOR FETCH, UPDATE, OR DELETE, OR THE RESULT OF A QUERY IS AN EMPTY TABLE

SQLCODE 110, Warning: SQL UPDATE TO A DATA CAPTURE TABLE NOT SIGNALED TO ORIGINATING SUBSYSTEM

SQLCODE 111, Warning: THE SUBPAGES OPTION IS NOT SUPPORTED FOR TYPE 2 INDEXES

SQLCODE 117, Warning: THE NUMBER OF INSERT VALUES IS NOT THE SAME AS THE NUMBER OF OBJECT COLUMNS

SQLCODE 162, Warning: TABLE SPACE . HAS BEEN PLACED IN CHECK PENDING

SQLCODE 203, Warning: THE QUALIFIED COLUMN NAME WAS RESOLVED USING A NON-UNIQUE OR UNEXPOSED NAME

SQLCODE 204, Warning: IS AN UNDEFINED NAME

SQLCODE 206, Warning: IS NOT A COLUMN OF AN INSERTED TABLE, UPDATED TABLE, OR ANY TABLE IDENTIFIED IN A FROM CLAUSE

SQLCODE 218, Warning: THE SQL STATEMENT REFERENCING A REMOTE OBJECT CANNOT BE EXPLAINED

SQLCODE 219, Warning: THE REQUIRED EXPLANATION TABLE DOES NOT EXIST

SQLCODE 220, Warning: THE COLUMN IN EXPLANATION TABLE IS NOT DEFINED PROPERLY

SQLCODE 236, Warning: SQLDA INCLUDES SQLVAR ENTRIES, BUT ARE REQUIRED FOR COLUMNS

SQLCODE 237, Warning: SQLDA INCLUDES SQLVAR ENTRIES, BUT ARE REQUIRED BECAUSE AT LEAST ONE OF THE COLUMNS
BEING DESCRIBED IS A DISTINCT TYPE

SQLCODE 238, Warning: SQLDA INCLUDES SQLVAR ENTRIES, BUT SQLVAR ENTRIES ARE NEEDED FOR COLUMNS BECAUSE
AT LEAST ONE OF THE COLUMNS BEING DESCRIBED IS A LOB

SQLCODE 239, Warning: SQLDA INCLUDES SQLVAR ENTRIES, BUT ARE REQUIRED FOR COLUMNS BECAUSE AT LEAST ONE
OF THE COLUMNS BEING DESCRIBED IS A DISTINCT TYPE

SQLCODE 304, Warning: A VALUE WITH DATA TYPE CANNOT BE ASSIGNED TO A HOST VARIABLE BECAUSE THE VALUE IS
NOT WITHIN THE RANGE OF THE HOST VARIABLE IN POSITION WITH DATA TYPE

SQLCODE 331, Warning: THE NULL VALUE HAS BEEN ASSIGNED TO A HOST VARIABLE BECAUSE THE STRING CANNOT BE
TRANSLATED. REASON , CHARACTER , HOST VARIABLE

SQLCODE 339, Warning: THE SQL STATEMENT HAS BEEN SUCCESSFULLY EXECUTED BUT THERE MAY BE SOME CHARACTER
CONVERSION INCONSISTENCIES

SQLCODE 394, Warning: USER SPECIFIED OPTIMIZATION HINTS USED DURING ACCESS PATH SELECTION

SQLCODE 395, Warning: USER SPECIFIED OPTIMIZATION HINTS ARE INVALID (REASON CODE = ''). THE OPTIMIZATION HINTS ARE IGNORED

SQLCODE 402, Warning: LOCATION IS UNKNOWN

SQLCODE 403, Warning: THE LOCAL OBJECT REFERENCED BY THE CREATE ALIAS STATEMENT DOES NOT EXIST

SQLCODE 434, Warning: OPTION IS A DEPRECATED FEATURE

SQLCODE 445, Warning: VALUE HAS BEEN TRUNCATED

SQLCODE 462, Warning: EXTERNAL FUNCTION OR PROCEDURE (SPECIFIC NAME ) HAS RETURNED A WARNING SQLSTATE, WITH DIAGNOSTIC TEXT

SQLCODE 464, Warning: PROCEDURE RETURNED QUERY RESULT SETS, WHICH EXCEEDS THE DEFINED LIMIT

SQLCODE 466, Warning: PROCEDURE RETURNED QUERY RESULT SETS

SQLCODE 494, Warning: NUMBER OF RESULT SETS IS GREATER THAN NUMBER OF LOCATORS

SQLCODE 495, Warning: ESTIMATED PROCESSOR COST OF PROCESSOR SECONDS ( SERVICE UNITS) IN COST CATEGORY
EXCEEDS A RESOURCE LIMIT WARNING THRESHOLD OF SERVICE UNITS

SQLCODE 535, Warning: THE RESULT OF THE POSITIONED UPDATE OR DELETE MAY DEPEND ON THE ORDER OF THE ROWS

SQLCODE 541, Warning: THE REFERENTIAL OR UNIQUE CONSTRAINT HAS BEEN IGNORED BECAUSE IT IS A DUPLICATE

SQLCODE 551, Warning: DOES NOT HAVE THE PRIVILEGE TO PERFORM OPERATION ON OBJECT

SQLCODE 552, Warning: DOES NOT HAVE THE PRIVILEGE TO PERFORM OPERATION

SQLCODE 558, Warning: THE WITH GRANT OPTION IS IGNORED

SQLCODE 561, Warning: THE ALTER, INDEX, REFERENCES, AND TRIGGER PRIVILEGES CANNOT BE GRANTED TO PUBLIC AT ALL LOCATIONS

SQLCODE 562, Warning: A GRANT OF A PRIVILEGE WAS IGNORED BECAUSE THE GRANTEE ALREADY HAS THE PRIVILEGE FROM THE GRANTOR

SQLCODE 585, Warning: THE SCHEMA NAME APPEARS MORE THAN ONCE IN THE CURRENT PATH

SQLCODE 599, Warning: COMPARISON FUNCTIONS ARE NOT CREATED FOR A DISTINCT TYPE BASED ON A LONG STRING DATA TYPE

SQLCODE 610, Warning: A CREATE/ALTER ON OBJECT HAS PLACED OBJECT IN PENDING

SQLCODE 625, Warning: THE DEFINITION OF TABLE HAS BEEN CHANGED TO INCOMPLETE

SQLCODE 626, Warning: DROPPING THE INDEX TERMINATES ENFORCEMENT OF THE UNIQUENESS OF A KEY THAT WAS DEFINED
WHEN THE TABLE WAS CREATED

SQLCODE 645, Warning: WHERE NOT NULL IS IGNORED BECAUSE THE INDEX KEY CANNOT CONTAIN NULL VALUES

SQLCODE 650, Warning: THE TABLE BEING CREATED OR ALTERED CAN NOT BECOME A DEPENDENT TABLE

SQLCODE 653, Warning: TABLE IN PARTITIONED TABLE SPACE IS NOT AVAILABLE BECAUSE ITS PARTITIONED INDEX HAS
NOT BEEN CREATED

SQLCODE 655, Warning: STOGROUP HAS BOTH SPECIFIC AND NON-SPECIFIC VOLUME IDS. IT WILL NOT BE ALLOWED IN
FUTURE RELEASES.

SQLCODE 658, Warning: THE SUBPAGES VALUE IS IGNORED FOR THE CATALOG INDEX

SQLCODE 664, Warning: THE INTERNAL LENGTH OF THE LIMIT-KEY FIELDS FOR THE PARTITIONED INDEX EXCEEDS THE
LENGTH IMPOSED BY THE INDEX MANAGER

SQLCODE 738, Warning: DEFINITION CHANGE OF MAY REQUIRE SIMILAR CHANGE ON READ-ONLY SYSTEMS

SQLCODE 799, Warning: A SET STATEMENT REFERENCES A SPECIAL REGISTER THAT DOES NOT EXIST AT THE SERVER SITE

SQLCODE 802, Warning: EXCEPTION ERROR HAS OCCURRED DURING OPERATION ON DATA, POSITION

SQLCODE 806, Warning: BIND ISOLATION LEVEL RR CONFLICTS WITH TABLESPACE LOCKSIZE PAGE OR LOCKSIZE ROW AND LOCKMAX 0

SQLCODE 807, Warning: THE RESULT OF DECIMAL MULTIPLICATION MAY CAUSE OVERFLOW

SQLCODE 863, Warning: THE CONNECTION WAS SUCCESSFUL BUT ONLY SBCS WILL BE SUPPORTED

SQLCODE 883, Warning: ROLLBACK TO SAVEPOINT OCCURRED WHEN THERE WERE OPERATIONS THAT CANNOT BE UNDONE OR AN
OPERATION THAT CANNOT BE UNDONE OCCURRED WHEN THERE WAS A SAVEPOINT OUTSTANDING

Top of Page

REXX compiler

2009-12-10T12:03:00.001-08:00

REXXCLG (FANCMCLG)

IBM Compiler and Library for REXX on zSeries V1R4 User's Guide and Reference
SH19-8160-05

REXXCLG compiles, link-edits, and runs a REXX program of type OBJECT. FANCMCLG is located in the data set prefix.SFANPRC.

//******************************************************************
//*
//* REXXCLG Compile, link edit, and run a REXX program of OBJ type.
//*
//* Licensed Materials - Property of IBM
//* 5695-013 IBM REXX Compiler
//* (C) Copyright IBM Corp. 1989, 2003
//*
//* Change Activity:
//* 03-05-28 Release 4.0
//*
//******************************************************************
//*
//* Parameters:
//*
//* OPTIONS Compilation options.
//* Default: XREF OBJECT NOCEXEC
//*
//* COMPDSN DSN of IBM REXX Compiler load library.
//*
//* LIBDSN DSN of IBM REXX Library load library for Stubs.
//*
//* LIBLPA DSN of IBM REXX Library LPA library.
//* If &LIBLPA is in the search order, you may deactivate
//* the GO.STEPLIB and the PROC LIBLPA definition.
//*
//* LIBXDSN DSN of IBM REXX Library exec library.
//*
//* Required:
//*
//* REXX.SYSIN DDNAME, REXX program to be compiled, link edited,
//* and run.
//*
//* Example:
//*
//* To compile MYREXX.EXEC(MYPROG), to link edit the resulting
//* OBJECT output together with a stub suitable for invocation
//* in MVS batch, to keep the resulting load module in
//* MYREXX.LOAD(MYPROG), and to run this load module, use the
//* following invocation:
//*
//* //S1 EXEC REXXCLG
//* //REXX.SYSIN DD DSN=MYREXX.EXEC(MYPROG),DISP=SHR
//* //LKED.SYSLMOD DD DSN=MYREXX.LOAD(MYPROG),DISP=SHR
//*
//* Modifications:
//* Change #HLQREXX to the appropriate high-level qualifier of
//* your installation.
//*
//******************************************************************
//*
//REXXCLG PROC STUB=MVS, Type of stub
// OPTIONS='XREF OBJECT NOCEXEC', REXX Compiler options
// COMPDSN='#HLQREXX.SFANLMD', REXX Compiler load lib
// LIBDSN='#HLQREXX.SEAGLMD', REXX Library stub load
// LIBLPA='#HLQREXX.SEAGLPA', REXX Library LPA lib
// LIBXDSN='#HLQREXX.SEAGCMD' REXX Library exec lib
//*
//*-----------------------------------------------------------------
//* Compile REXX program.
//*-----------------------------------------------------------------
//*
//REXX EXEC PGM=REXXCOMP,PARM='&OPTIONS'
//STEPLIB DD DSN=&COMPDSN,DISP=SHR
//SYSPRINT DD SYSOUT=*
//SYSTERM DD SYSOUT=*
//*SYSIEXEC DD DUMMY
//*SYSDUMP DD DUMMY
//*SYSCEXEC DD DUMMY
//SYSPUNCH DD DSN=&&OBJECT,DISP=(MOD,PASS),UNIT=SYSDA,
// SPACE=(800,(800,100))
//*
//*-----------------------------------------------------------------
//* Prepare SYSLIN data set for subsequent link step.
//*-----------------------------------------------------------------
//*
//PLKED EXEC PGM=IRXJCL,PARM='REXXL &STUB',
// COND=(9,LT,REXX)
//*
//SYSEXEC DD DSN=&LIBXDSN,DISP=SHR
//SYSIN DD DSN=&&OBJECT,DISP=(OLD,DELETE)
//SYSTSPRT DD SYSOUT=*
//SYSOUT DD DSN=&&SYSOUT,DISP=(MOD,PASS),UNIT=SYSDA,
// DCB=(RECFM=FB,LRECL=80,BLKSIZE=800),
// SPACE=(800,(800,100))
//*
//*-----------------------------------------------------------------
//* Link together stub and program.
//*-----------------------------------------------------------------
//*
//LKED EXEC PGM=HEWL,PARM='LIST,AMODE=31,RMODE=ANY,RENT,MAP',
// COND=((9,LT,REXX),(0,NE,PLKED))
//*
//SYSLIN DD DSN=&&SYSOUT,DISP=(OLD,DELETE)
//SYSLIB DD DSN=&LIBDSN,DISP=SHR
//SYSUT1 DD UNIT=SYSDA,SPACE=(1024,(200,20))
//SYSPRINT DD SYSOUT=*
//SYSLMOD DD DSN=&&GOSET(GO),DISP=(MOD,PASS),UNIT=SYSDA,
// SPACE=(1024,(50,20,1))
//*
//*-----------------------------------------------------------------
//* Run the compiled REXX program.
//*-----------------------------------------------------------------
//*
//GO EXEC PGM=*.LKED.SYSLMOD,
// COND=((9,LT,REXX),(0,NE,PLKED),(0,NE,LKED))
//*
//STEPLIB DD DSN=&LIBLPA,DISP=SHR
//SYSTSPRT DD SYSOUT=*

SCL SCAN SEARCH LIST MEMBER

2009-12-10T09:06:00.000-08:00

SCL ENDEVOR SEARCH SCAN

IN THE J0B LIST IN SDSF: F '*' 12

SET STOPRC 16 .
LIST ELEMENT 'SIDCH1PD' THRU 'SIDZ30PC'
FROM ENVIRONMENT 'PROD' SYSTEM 'CIS' SUBSYSTEM 'CIS00'
TYPE 'COB2' STAGE 2
TO DSNAME 'RSXRRA.SQL.OUT'
OPTIONS SHOWING TEXT NOSEARCH
WHERE TEXT ((EQ
'AP_EMPLOYER'
COLUMNS 006 072 ) AND
( EQ 'INSERT'
COLUMNS 006 072 ))
BUILD ACTION &&ACTION
.

--------===========================================================------------------

SET STOPRC 16 .
LIST ELEMENT 'SIDCH1PD' THRU 'SIDZ30PC'
FROM ENVIRONMENT 'PROD' SYSTEM 'CIS' SUBSYSTEM 'CIS00'
TYPE 'COB2' STAGE 2
TO DSNAME 'RSXRRA.SQL.OUT'
OPTIONS SHOWING TEXT NOSEARCH
WHERE TEXT
(
(
( EQ ' UPDATE VAP_EMPLOYER' COLUMNS 006 072 )
OR
( EQ ' UPDATE VAP_EMPLOYER' COLUMNS 006 072 )
OR
( EQ ' INTO VAP_EMPLOYER' COLUMNS 006 072 )
OR
( EQ ' INTO VAP_EMPLOYER' COLUMNS 006 072 )
)

)
BUILD ACTION &&ACTION
.
****************************

LIST ELEMENT 'SID*'
FROM ENVIRONMENT 'PROD' SYSTEM 'CIS' SUBSYSTEM 'CIS00'
TYPE 'COB2' STAGE 2
OPTIONS SHOWING TEXT
WHERE TEXT EQ
'ESCROW_ORDER'
.
**************************** Bottom of Data ********************

SQL collections

2009-11-04T09:39:00.001-08:00

SELECT *
FROM SYSIBM.SYScolumns where tbname in ( select name from sysibm.systables
WHERE CREATOR ='SIDZ1008' and type = 'T' ) ;

select * from sysibm.syscolumns where TBNAME like '%QRTRLY_COLLECTNS%' ;

select * from SIDz1201.vQRTRLY_COLLECTNS
fetch first 10 rows only;

Queries
SELECT *
FROM SYSIBM.SYSINDEXES
WHERE CREATOR LIKE 'SIDZ1%'
AND TBNAME = 'TCASE'
AND UNIQUERULE = 'P'
WITH UR
FETCH FIRST 2000 ROWS ONLY;

insert into sidc3006.VEWO_HOLD_QUEUE( select IRN , ENTRY_TS, date(entry_ts) + 20 days
from sidc3002.VAP_EMPLOYER_REPLY where irn in (400031231 ,
405009120 ,
406008707 ,
406008712 ,
406008750 ,
406009074 ,
406009167 ,
407008436 ,
408008505 ,
410009029 ,
412008749 ,
412008751 ,
413008669 ,
418009859 ,
443009801 ,
444009581 ,
454009774 ,
458009203 ,
459009204
) )

select * from sidc3006.VEWO_HOLD_QUEUE

select * from sidc3008.vescrow_hist where case_num = 11947 ;
select * from sidc3008.vfunds_in_escrow where case_num = 11947 ;

select * from sidc3008.vaccount where case_num = 11947 ;

insert into sidc3008.vfunds_in_escrow( select ESCROW_TYPE, RCPT_NUM, ' ' , CREATION_DT, ESCROW_AMT , 0 , JURISD, PAYEE_CL_IND, PRV_RET_DISBUR_IND, ' ', DNLOAD_NUM, FIPS_CD, ' ', ACCT_TYPE, CRTORD_NUM, CASE_NUM, IRN, CRTORD_FIPS_CD, ESCROW_ORDER, 0, APPROVE_DT, ' ', AU_NUM from sidc3008.vescrow_hist where case_num = 11947 and acct_type = 'CC' ) ;

INSERT MANY ROWS
EXEC SQL
INSERT INTO DSN8810.ACT
(ACTNO, ACTKWD, ACTDESC)
VALUES (:HVA1, :HVA2, :HVA3 :IVA3)
FOR :NUM-ROWS ROWS
END-EXEC.

select distinct a.ap_irn from sidz1004.vcase a , SIDz1002.VAP_EMPLOYER_REPLy b
where a.ap_irn <> b.irn
and a.case_type = 'NI'
and a.case_status = 'ATV'
and a.case_num > 100
fetch first 20 rows only

select a.ap_irn , count(*) from sidz1004.vcase a , SIDz1002.VAP_EMPLOYER_REPLy b
where a.ap_irn = b.irn
and a.case_type = 'NI'
and a.case_status = 'ATV'
and a.case_num > 100

group by a.ap_irn ;

;

select * from sidst004.vcase a
where a.ap_irn = 491009280 ;

select case_num , CASE_ACTLG_DT, userid , count(*) from SIDz1004.Vcase_Action_log
where case_num > 100
and case_actlg_msg1 like 'NEW AP EMPLOYER DATA FROM%'

group by case_num , CASE_ACTLG_DT, userid
having count(*) > 1

select * from sidc3008.vescrow_hist where case_num = 11947 ;
select case_num , CASE_ACTLG_DT, userid , count(*) from SIDz1004.Vcase_Action_log
where case_num > 100
and case_actlg_msg1 like 'NEW AP EMPLOYER DATA FROM%'

group by case_num , CASE_ACTLG_DT, userid
having count(*) > 1

select * from sidc3008.vescrow_hist where case_num = 11947 ;
select * from sidc3008.vfunds_in_escrow where case_num = 11947 ;

select * from sidc3008.vfunds_in_escrow FETCH FIRST 200 ROWS only ;

Insert into sidc3004.vpip_agreemnt(
CASE_NUM, APLN_SEQ_NUM, APLN_DT, APLN_DECISION_DT,
APLN_DECISION_CD, SIGNED_DT, PGM_START_DT, ARREAR_START_AMT,
CMPLD_12MONTH_IND, CMPLD_12MONTH_DT, ARREAR_12MONTH_AMT,
CMPLD_24MONTH_IND, CMPLD_24MONTH_DT, ARREAR_24MONTH_AMT,
EXIT_REASON_CD, EXIT_DT, LAST_BREACH_DT, MONTHS_PROC_NUM,
LAST_UPD_USER_ID, LAST_UPD_PGM_NAM, LAST_UPD_TS )

select
distinct a.case_num ,
APLN_SEQ_NUM, APLN_DT, APLN_DECISION_DT,
APLN_DECISION_CD, SIGNED_DT, PGM_START_DT, ARREAR_START_AMT,
CMPLD_12MONTH_IND, CMPLD_12MONTH_DT, ARREAR_12MONTH_AMT,
CMPLD_24MONTH_IND, CMPLD_24MONTH_DT, ARREAR_24MONTH_AMT,
EXIT_REASON_CD, EXIT_DT, LAST_BREACH_DT, MONTHS_PROC_NUM,
LAST_UPD_USER_ID, LAST_UPD_PGM_NAM, LAST_UPD_TS

from sidc3008.vaccount a , sidc3004.vpip_agreemnt b
where a.crtord_eff_dt between 19971100 and 20071111
and b.case_num = 11057
and a.case_num between 11057 and 110000
--fetch first 10 ROWS only

select
(select distinct case_num "case_num" from sidc3008.vaccount
where crtord_eff_dt between 19971100 and 20071111
) ,

max(APLN_SEQ_NUM) ,
max( APLN_DT) ,
max( APLN_DECISION_DT) ,
max(APLN_DECISION_CD) ,
max( SIGNED_DT) ,
max( PGM_START_DT) ,
max( ARREAR_START_AMT) ,
max(CMPLD_12MONTH_IND) ,
max( CMPLD_12MONTH_DT) ,
max( ARREAR_12MONTH_AMT) ,
max(CMPLD_24MONTH_IND) ,
max( CMPLD_24MONTH_DT) ,
max( ARREAR_24MONTH_AMT) ,
max(EXIT_REASON_CD) ,
max( EXIT_DT) ,
max( LAST_BREACH_DT) ,
max( MONTHS_PROC_NUM) ,
max(LAST_UPD_USER_ID) ,
max( LAST_UPD_PGM_NAM) ,
max( LAST_UPD_TS)

from sidc3004.vpip_agreemnt where

sidc3004.vpip_agreemnt.case_num = 11057

apr-30

select * from SIDST002.VAP_EMPLOYER_REPLY where irn in ( 449008657, 418008765)
order by ENTRY_TS desc
fetch first 200 rows only;

select case_num, ap_irn from SIDST004.vcase where case_num in ( 59001161, 180011422, 11471)

410009029

select distinct a.tbl_id, a.codexn from sidz1010.vcodes a , sidz1010.vcodes b where a.CODEXN in ( '30' , '32' , '60' , '62' )
and b.TBL_ID = a.TBL_ID ;

select a.tbl_id , count(*) from sidz1010.vcodes a where a.CODEXN in ( '30' , '32' , '60' , '62' )
and exists ( select b.TBL_ID from sidz1010.vcodes b where b.TBL_ID = a.TBL_ID )
group by a.tbl_id order by 2 desc ;

select a.case_num , b.irn, a.case_status from sidst004.Vcase a , sidst002.Vabsent_parent b where
a.case_num in (990011281,640011900,490011717,820012388,50011322,220011070,210011983)
and a.ap_irn = b.irn

EXEC SQL
DECLARE PENDDRV_CSR INSENSITIVE SCROLL CURSOR
WITH ROWSET POSITIONING FOR
SELECT DISTINCT
T595.ID_DVN
,T595.NA_VENDOR
,T003.ID_INDV_DCN
,T003.NA_INDV_LAST ||' ,' || T003.NA_INDV_FIRST || ' '
|| T003.NA_INDV_MID || ' ' || T003.NA_INDV_SFX
,T680.TS_PNDPAYLI_PK
,COALESCE(CHAR(T680.TS_NOTES_FK), ' ')
,COALESCE(CHAR(T680.TS_PLACEMENT_FK), ' ')
,T680.NO_VENDOR_BILLING
,T680.AM_TOTAL_LINE_ITEM
,T680.CO_PROGRAM_AREA
,( SELECT LEFT(A18.TX_CODE_DESC, 100) FROM
SFCWT018_CODE_TBLE A18 WHERE
A18.CO_CODE_VALUE = T680.CO_PROGRAM_AREA
AND A18.DA_END IS NULL
AND A18.CO_CODE_TYPE = 'PROGRAMAREA' )
,T680.CO_FUND
,IFNULL(( SELECT LEFT(B18.TX_CODE_DESC, 100) FROM
SFCWT018_CODE_TBLE B18 WHERE
B18.CO_CODE_VALUE = T680.CO_FUND
AND B18.DA_END IS NULL
AND B18.CO_CODE_TYPE = 'FUNDCATEGORY' ), ' ' )
,T680.CO_SERVICE
,IFNULL(( SELECT MAX(left(T569.TX_DESCRIPTION,60)) FROM
SFCWT569_SRVAMDCD T569 WHERE
T569.CO_SVC_AMEND = T680.CO_SERVICE
AND T569.CO_PROGRAM_AREA =T680.CO_PROGRAM_AREA), ' ' )
,T680.DA_SERVICE_BEGIN
,T680.DA_SERVICE_END
,T680.AM_TOTAL_UNITS
,T680.CO_VENDOR_TYPE
,IFNULL(( SELECT LEFT(V18.TX_CODE_DESC, 50) FROM
SFCWT018_CODE_TBLE V18 WHERE
V18.CO_CODE_VALUE = T680.CO_VENDOR_TYPE
AND V18.DA_END IS NULL
AND V18.CO_CODE_TYPE = 'VENDORTYPE' ), ' ' )
,T680.CO_PAY_COUNTY
,T680.CO_PAY_OFFICE
,T680.CO_ERROR_TYPE
,T680.SW_DO_NOT_PAY
FROM
SFCWT595_VENDOR T595
,SFCWT003_INDV T003
,SFCWT680_PNDPAYLI T680
WHERE
T680.TS_INDV_FK = T003.TS_INDV_PK
AND T680.TS_VENDOR_FK = T595.TS_VENDOR_PK

AND ( T680.CO_PROGRAM_AREA = :T680-CO-PROGRAM-AREA
OR :T680-CO-PROGRAM-AREA = ' ' )
AND ( T680.CO_PAY_COUNTY =:T680-CO-PAY-COUNTY
OR :T680-CO-PAY-COUNTY = ' ' )
AND ( T680.CO_SERVICE = :T680-CO-SERVICE
OR :T680-CO-SERVICE = ' ' )
AND ( T680.DA_SERVICE_BEGIN >= :T680-DA-SERVICE-BEGIN )
* OR :T680-DA-SERVICE-BEGIN = '0000-00-00' )
AND ( T680.DA_SERVICE_END <= :T680-DA-SERVICE-END )
* OR :T680-DA-SERVICE-END = '9999-99-99' )

AND ( T003.ID_INDV_DCN = :T003-ID-INDV-DCN
OR :T003-ID-INDV-DCN = ' ' )

AND ( T595.ID_DVN = :T595-ID-DVN
OR :T595-ID-DVN = ' ' )

AND ( T680.SW_CAREERPRNT_PMT = :T680-SW-CAREERPRNT-PMT
OR :T680-SW-CAREERPRNT-PMT = ' ' )

AND ( T680.SW_CLIENT_PMT = :T680-SW-CLIENT-PMT
OR :T680-SW-CLIENT-PMT = ' ' )

AND ( T680.SW_VENDOR_PMT = :T680-SW-VENDOR-PMT
OR :T680-SW-VENDOR-PMT = ' ' )

END-EXEC
EXEC SQL
OPEN PENDDRV_CSR
END-EXEC
IF SQLCODE < 0
PERFORM 9900-SQL-ERROR
END-IF.
INITIALIZE WS-ROWSET-AREA

EXEC SQL
FETCH ROWSET STARTING AT
ABSOLUTE :WS-CSRPOS FROM PENDDRV_CSR
FOR :WS-RECS-TO-FETCH ROWS
INTO
:WS-RS-ID-DVN
,:WS-RS-VENDOR-NAME
,:WS-RS-ID-DCN
,:WS-RS-CLNT-NAME
,:WS-RS-PYMT-LN-PK
,:WS-RS-NOTES-FK
,:WS-RS-PYMT-LN-ITEM-PK
,:WS-RS-TRACKIN-NO
,:WS-RS-AM-LINE-ITEM
,:WS-RS-PROGRAM-AREA
,:WS-RS-PGM-DESC
,:WS-RS-CO-FUND
,:WS-RS-FUND-DESC
,:WS-RS-CO-SERV
,:WS-RS-SERV-DESC
,:WS-RS-DA-SERV-BEGIN
,:WS-RS-DA-SERV-END
,:WS-RS-NO-UNITS
,:WS-RS-VENDOR-TYPE
,:WS-RS-VEND-DESC
,:WS-RS-CO-PAY-COUNTY
,:WS-RS-CO-PAY-OFFICE
,:WS-RS-CO-TYPE
,:WS-RS-PYMNT-HIST-SW
END-EXEC
EVALUATE TRUE
WHEN SQLCODE < 0
PERFORM 9900-SQL-ERROR
WHEN OTHER
MOVE SQLERRD(3) TO WS-RECS-FETCHD
IF WS-RECS-FETCHD = WS-RECS-TO-FETCH
MOVE 'Y' TO FCWCP812-SHOW-NEXT
SUBTRACT 1 FROM WS-RECS-FETCHD
END-IF
END-EVALUATE

EXEC SQL
CLOSE PENDDRV_CSR
END-EXEC
IF SQLCODE < 0
PERFORM 9900-SQL-ERROR
END-IF.

INITIALIZE FCWCP812-ROWSET-DVNDCN
MOVE WS-PAGE-NO TO FCWCP812-PAGE-NUM
PERFORM VARYING WS-INDEX FROM +1 BY +1
UNTIL WS-INDEX > WS-RECS-FETCHD
OR WS-INDEX > 20
MOVE WS-RS-ID-DVN (WS-INDEX) TO
FCWCP812-RS-ID-DVN (WS-INDEX)
MOVE WS-RS-VENDOR-NAME (WS-INDEX) TO
FCWCP812-RS-VENDOR-NAME (WS-INDEX)
MOVE WS-RS-VEND-DESC (WS-INDEX) TO
FCWCP812-RS-VEND-DESC (WS-INDEX)
MOVE WS-RS-ID-DCN (WS-INDEX) TO
FCWCP812-RS-ID-DCN (WS-INDEX)
MOVE WS-RS-CLNT-NAME (WS-INDEX) TO
FCWCP812-RS-CLNT-NAME (WS-INDEX)
MOVE WS-RS-PYMT-LN-PK (WS-INDEX) TO
FCWCP812-RS-PYMT-LN-PK (WS-INDEX)
MOVE WS-RS-PYMT-LN-ITEM-PK(WS-INDEX) TO
FCWCP812-RS-PYMT-LN-ITEM-PK(WS-INDEX)
MOVE WS-RS-NOTES-FK (WS-INDEX) TO
FCWCP812-RS-NOTES-FK (WS-INDEX)
MOVE WS-RS-TRACKIN-NO (WS-INDEX) TO
FCWCP812-RS-TRACKIN-NO (WS-INDEX)

MOVE WS-RS-AM-LINE-ITEM (WS-INDEX) TO
FCWCP812-RS-AM-LINE-ITEM (WS-INDEX)
MOVE WS-RS-PGM-DESC (WS-INDEX) TO
FCWCP812-RS-PGM-DESC (WS-INDEX)
MOVE WS-RS-FUND-DESC (WS-INDEX) TO
FCWCP812-RS-FUND-DESC (WS-INDEX)
MOVE WS-RS-FUND-DESC (WS-INDEX) TO
FCWCP812-RS-FUND-DESC (WS-INDEX)
MOVE WS-RS-SERV-DESC (WS-INDEX) TO
FCWCP812-RS-SERV-DESC (WS-INDEX)
MOVE WS-RS-DA-SERV-BEGIN(WS-INDEX) TO
FCWCP812-RS-DA-SERV-BEGIN (WS-INDEX)
MOVE WS-RS-DA-SERV-END (WS-INDEX) TO
FCWCP812-RS-DA-SERV-END (WS-INDEX)
MOVE WS-RS-NO-UNITS (WS-INDEX) TO
FCWCP812-RS-NO-UNITS (WS-INDEX)
IF WS-RS-CO-PAY-COUNTY(WS-INDEX) > SPACES
INITIALIZE FCWCP462-PARMS
MOVE WS-RS-CO-PAY-COUNTY(WS-INDEX)
TO FCWCP462-CO-FIPS-IN
MOVE WS-RS-CO-PAY-OFFICE(WS-INDEX)
TO FCWCP462-CO-OFFICE-IN
SET FCWCP462-ACTION-CNTY-OFF TO TRUE
EXEC CICS
LINK PROGRAM('FCWSC462')
COMMAREA(FCWCP462-PARMS)
LENGTH(LENGTH OF FCWCP462-PARMS)
END-EXEC
IF FCWCP462-ERRMSG <= SPACES
STRING FCWCP462-COUNTY-NAME DELIMITED BY ' '
' - ' DELIMITED BY SIZE
FCWCP462-CO-FIPS-IN DELIMITED BY SIZE
'/' DELIMITED BY SIZE
FCWCP462-CO-OFFICE-IN DELIMITED BY SIZE
'(' DELIMITED BY SIZE
FCWCP462-NA-OFFICE DELIMITED BY ' '
')' DELIMITED BY SIZE
INTO FCWCP812-RS-CNTY-DESC (WS-INDEX)
END-STRING
ELSE
MOVE SPACES TO FCWCP812-RS-CNTY-DESC (WS-INDEX)
MOVE 'LINK TO FCWSC462 ERROR' TO FCWCP812-ERRMSG
END-IF
END-IF
MOVE WS-RS-CNTY-DESC (WS-INDEX) TO
FCWCP812-RS-CNTY-DESC (WS-INDEX)
MOVE WS-RS-CO-TYPE (WS-INDEX) TO
FCWCP812-RS-CO-TYPE (WS-INDEX)
MOVE WS-RS-PYMNT-HIST-SW (WS-INDEX) TO
FCWCP812-RS-PYMNT-HIST-SW (WS-INDEX)
END-PERFORM
.
5100-PAYMENT-DTL-EXIT.
EXIT.

http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db2.doc.relg/fmlt.htm

Example: The following statement declares a row set cursor:

EXEC SQL
DECLARE C1 CURSOR
WITH ROWSET POSITIONING FOR
SELECT EMPNO, LASTNAME, SALARY
FROM DSN8810.EMP
END-EXEC.

To tell DB2 that you are ready to process the first row set of the result table, execute the OPEN statement in your program. DB2 then uses the SELECT statement within the DECLARE CURSOR statement to identify the rows in the result table.
Using a multiple-row FETCH statement with host variable arrays

When your program executes a FETCH statement with the ROWSET keyword, the cursor is positioned on a row set in the result table. That row set is called the current row set. Declare the dimension of each of the host variable arrays to be greater than or equal to the number of rows that are to be retrieved.

Example: The following FETCH statement retrieves 20 rows into host variable arrays that are declared in your program:

EXEC SQL
FETCH NEXT ROWSET FROM C1
FOR 20 ROWS
INTO :HVA-EMPNO, :HVA-LASTNAME, :HVA-SALARY :INDA-SALARY
END-EXEC.

Using a multiple-row FETCH statement with a descriptor

Suppose that you want to dynamically allocate the necessary storage for the arrays of column values that are to be retrieved from the employee table. You must do the following steps:

1. Declare an SQLDA structure.
2. Dynamically allocate the SQLDA and the necessary arrays for the column values.
3. Set the fields in the SQLDA for the column values that are to be retrieved.
4. Open the cursor.
5. Fetch the rows.

After allocating the SQLDA and the necessary arrays for the column values, you must set the fields in the SQLDA.

Example: After the OPEN statement, the program fetches the next row set by using the following statement:

EXEC SQL
FETCH NEXT ROWSET FROM C1
FOR 20 ROWS
USING DESCRIPTOR :outsqlda;

The USING clause of the FETCH statement names the SQLDA that describes the columns that are to be retrieved.
Using row-set positioned UPDATE statements

After your program executes a FETCH statement to establish the current row set, you can use a positioned UPDATE statement with either of the following clauses:

* WHERE CURRENT OF cursor-name to update:
o a single row if the cursor is on a single row
o all the rows of a row set if the cursor is on a row set
* WHERE CURRENT OF cursor-name FOR ROW n OF ROWSET to update only row n of the current row set

Updating all rows of the current row set: The following positioned UPDATE statement uses the WHERE CURRENT OF clause:

EXEC SQL
UPDATE DSN8810.EMP
SET SALARY = 50000
WHERE CURRENT OF C1
END-EXEC.

When the UPDATE statement is executed, the cursor must be positioned on a row or row set of the result table. If the cursor is positioned on a row, that row is updated. If the cursor is positioned on a row set, all of the rows in the row set are updated.

Updating a specific row of the current row set: The following positioned UPDATE statement uses the WHERE CURRENT OF cursor FOR ROW n OF ROWSET clause:

EXEC SQL
UPDATE DSN8810.EMP
SET SALARY = 50000
WHERE CURRENT OF C1 FOR ROW 5 OF ROWSET
END-EXEC.

When the UPDATE statement is executed, the cursor must be positioned on a row set of the result table. The specified row (in the example, row 5) of the current row set is updated.
Using row-set positioned DELETE statements

After your program executes a FETCH statement to establish the current row set, you can use a positioned DELETE statement with either of the following clauses:

* WHERE CURRENT OF cursor-name to delete:
o a single row if the cursor is on a single row
o all the rows of a row set if the cursor is on a row set
* WHERE CURRENT OF cursor-name FOR ROW n OF ROWSET to delete only row n of the current row set

Deleting all rows of the current row set: The following positioned DELETE statement uses the WHERE CURRENT OF clause:

EXEC SQL
DELETE FROM DSN8810.EMP
WHERE CURRENT OF C1
END-EXEC.

When the DELETE statement is executed, the cursor must be positioned on a row or row set of the result table. If the cursor is positioned on a row, that row is deleted, and the cursor is positioned before the next row of its result table. If the cursor is positioned on a row set, all of the rows in the row set are deleted, and the cursor is positioned before the next row set of its result table.

Deleting a single row of the current row set: The following positioned DELETE statement uses the WHERE CURRENT OF cursor FOR ROW n OF ROWSET clause:

EXEC SQL
DELETE FROM DSN8810.EMP
WHERE CURRENT OF C1 FOR ROW 5 OF ROWSET
END-EXEC.

When the DELETE statement is executed, the cursor must be positioned on a row set of the result table. The specified row of the current row set is deleted, and the cursor remains positioned on that row set. The deleted row (in the example, row 5 of the row set) cannot be retrieved or updated.

OCSE34 DUPLICATE PROBLEM FIX :

SELECT A.CASE_NUM, A.JURISD, A.CRTORD_FIPS_CD, A.CRTORD_NUM,
A.ACCT_TYPE, A.RCPT_NUM, A.CASE_TYPE, A.CASE_SUBTYPE,
A.APPLIED_AMT, A.DBCR_CD, A.ENTRY_DT,
A.AU_NUM, A.ESCROW_GEN_FLG, A.POST_DT,

case
when 1 = ( select count(*) FROM SIDRS035.VDISB_ESCROW_WORK r

WHERE r.CASE_NUM = A.CASE_NUM
AND r.JURISD = A.JURISD
AND r.CRTORD_FIPS_CD = A.CRTORD_FIPS_CD
AND r.CRTORD_NUM = A.CRTORD_NUM
AND r.ACCT_TYPE = A.ACCT_TYPE
AND r.RCPT_NUM = A.RCPT_NUM )

then

(SELECT digits(C.CHECK_NUM) || char(C.CHECK_DT) || CHAR(C.CREATION_DT)
FROM SIDRS035.VDISB_ESCROW_WORK C
WHERE C.CASE_NUM = A.CASE_NUM
AND C.JURISD = A.JURISD
AND C.CRTORD_FIPS_CD = A.CRTORD_FIPS_CD
AND C.CRTORD_NUM = A.CRTORD_NUM
AND C.ACCT_TYPE = A.ACCT_TYPE
AND C.RCPT_NUM = A.RCPT_NUM

)

else

(SELECT max(digits(m.CHECK_NUM) || char(m.CHECK_DT) || CHAR(m.CREATION_DT))
FROM SIDRS035.VDISB_ESCROW_WORK m
WHERE m.CASE_NUM = A.CASE_NUM
AND m.JURISD = A.JURISD
AND m.CRTORD_FIPS_CD = A.CRTORD_FIPS_CD
AND m.CRTORD_NUM = A.CRTORD_NUM
AND m.ACCT_TYPE = A.ACCT_TYPE
AND m.RCPT_NUM = A.RCPT_NUM
AND m.CREATION_DT = A.ENTRY_DT
)

end case

FROM
SIDRS008.VDISTRIBUTION_HIST A
where exists
( select 1
FROM SIDRS035.VDISB_ESCROW_WORK E
WHERE E.CASE_NUM = A.CASE_NUM
AND E.JURISD = A.JURISD
AND E.CRTORD_FIPS_CD = A.CRTORD_FIPS_CD
AND E.CRTORD_NUM = A.CRTORD_NUM
AND E.ACCT_TYPE = A.ACCT_TYPE
AND E.RCPT_NUM = A.RCPT_NUM
)

order by A.JURISD, A.RCPT_NUM, A.CASE_NUM
WITH UR;

REXX

2009-11-04T09:33:00.000-08:00

SETUP

/* REXX */
ADDRESS TSO
"ALLOC FILE(SYSEXEC)
DA('SYSLOC.TSO.COMMON.EXEC.FB','DBDCLOC.TSO.S0F.EXEC.FB',
'SYS1.SISPEXEC','NDVR.EDVTOOLS.REXX','RSXRR3.ROMMY.EXEC')SHR REUSE"
ISPF

_________________________

exec 'rsxrr3.share.rexx(SETUP)' in the initial command b4 logon

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

KR-228* How to execute REXX codes

Addendum to the REXX crash course series.

4 ways to execute REXX codes

1.) Foreground execution

Prefix member name by EXEC within the member listing screen – the method we’ve been using so far.

2.) Another foreground method

Type TSO EXEC 'pds(rexx-member)' on the command line on any screen.

3.) Foreground again (most commonly used method)
• To run the rexx exec without giving specifying the path we can allocate the PDS containing the REXX member to SYSEXEC or SYSPROC -> these are the files that are searched to locate execs.
• Usually SYSPROC contains all user created CLISTS; so we can append our dataset to this (SYSPROC is like a dd-name to which datasets can be concatenated) -> use the ISRDDN command to check datasets allocated in your session.
• To allocate datasets to SYSPROC use the following TSO command: CONCAT FILE(SYSPROC) DA('pds name') SHR REUSE
• Now if your REXX code is called CREATOR then you can just say TSO CREATOR on any screen to invoke the tool. We can also say: %CREATOR
• One question is should I execute the CONCAT command each time I log into mainframes?
Yes - there are a couple of ways to circumvent this: take advantage of the initial logon screen where you can specify commands to be executed on logging in. Another option is that your mainframe might already have a specific dataset that is executed for each user on logging in; in this case just add the CONCAT step to this file.
4.) Running a REXX exec in batch

Write a jcl step to execute the tso program IKJEFT01.
In the SYSTSIN card we can give the TSO commands to be executed.
Ex:
//SYSTSIN DD * EX 'TEST.SS.REXX.EXEC(CREATOR)'
/*
If you want to pass arguments to your program, then use:
//SYSTSIN DD * %CREATOR arg1 arg2
/*
(CREATOR code should then have an ARG statement to capture the arguments).

http://www.tek-tips.com/viewthread.cfm?qid=1234003&page=9

tcurrier (Programmer)
23 May 06 10:55
In a Rexx exec, I am trying to concatenate 2 private libraries to ISPLLIB as follows:
CODE
ADDRESS TSO "ALLOC FI(MYLIB1) SHR DA('H2501S.T.DYNAMIC.LIBZ00')"
IF RC = 0 THEN CONCAT ISPLLIB MYLIB1
ADDRESS TSO "ALLOC FI(MYLIB2) SHR DA('H2501S.T.DYNAMIC.LIBZ01')"
IF RC = 0 THEN CONCAT ISPLLIB MYLIB2

I'm getting an error:

CONCAT- SPECIFIED DDNAME(S) ALREADY "OPEN"

Not sure if this belong in the 'Rexx' forum, but ... Thanks...

Tek-Tips Forums is Member Supported. Click Here to donate.
rexxhead (Programmer)
24 May 06 9:45
Not exactly REXX, but I don't believe there's an ISPF forum....

The error is because when ISPF starts, it OPENs all the ISPxLIB files. You can't concatenate to an already-open file. Not even CONCAT can do it.

Instead, you should LIBDEF:
CODE
address ISPEXEC "LIBDEF ISPPLIB DATASET ID('H2501S.T.DYNAMIC.LIBZ00') STACK"

This layers the datasets specified in "ID" above the current ISPLLIB. When you're finished using them,
CODE
address ISPEXEC "LIBDEF ISPPLIB"
and they evaporate.
Frank Clarke
Tampa Area REXX Programmers' Alliance
REXX Language Assn Listmaster

tcurrier (Programmer)
24 May 06 14:10
Thanks... using your method, I wasn't able to get it to work, though:

"ISPEXEC LIBDEF ISPLLIB DATASET ID('H2501S.T.DYNAMIC.LIBZ00') STACK"
"ISPEXEC LIBDEF ISPLLIB DATASET ID('H2501S.T.DYNAMIC.LIBZ01') STACK"

"ISPEXEC SELECT CMD(H2545Z00)"
IKJ56500I COMMAND H2545Z00 NOT FOUND

ADDRESS LINKPGM 'H2545Z00'
+++ RC(-3) +++

"ISPEXEC SELECT PGM(H2545Z00)"
Link to 'H2545Z00' failed, abend code = x'00000806'.
-------------------------------------------------------------
"ISPEXEC LIBDEF ISPLLIB DATASET
ID('H2501S.T.DYNAMIC.LIBZ00','H2501S.T.DYNAMIC.LIBZ01')"

"ISPEXEC SELECT PGM(H2545Z00)"
CEE3501S The module H2545Z01 was not found.
------------------------------------------------------------
"ISPEXEC LIBDEF ISPLLIB DATASET
ID('H2501S.T.DYNAMIC.LIBZ00','H2501S.T.DYNAMIC.LIBZ01')"

ADDRESS LINKPGM 'H2545Z00'
CEE3501S The module H2545Z01 was not found.
-------------------------------------------------------------
THIS IS THE ONLY WAY I COULD GET IT TO WORK :

"ISPEXEC LIBDEF ISPLLIB DATASET
ID('H2501S.T.DYNAMIC.LIBZ00','H2501S.T.DYNAMIC.LIBZ01')"

"ISPEXEC SELECT CMD(H2545Z00)"

*** NOW IN H2545Z00 ***
*** NOW IN H2545Z01 ***

--------------------------------------------------------------

kevinf2349 (TechnicalUser)
24 May 06 14:24
Thats because in the initial attempt you effectively caused the first LIBDEF to be thrown aside for the second one.

The way you eventually got it to work is the correct method.

rexxhead (Programmer)
25 May 06 10:50

He STACKed the LIBDEFs, so he actually has all of them available. Notice that he also switched to "SELECT CMD(" as Doug Nadel advised him to do over on MVSHELP. I think that was "the answer".
Frank Clarke
Tampa Area REXX Programmers' Alliance
REXX Language Assn Listmaster

kevinf2349 (TechnicalUser)
25 May 06 13:19
Whoops...my bad... I completely missed the STACK.

Example:

ALLOC FI(NOTCAT) DA('dsn.not.catalog') +
SHR REUSE UNIT(SYSALLDA) VOL(vvvvvv)
ALLOC FI(MYDD) DA('cat.dsn.#1' +
'cat.dsn.#2' +
'cat.dsn.#3') SHR REUSE
CONCAT (MYDD,NOTCAT)
OPENFILE MYDD INPUT
...

TSO ISRDDN

SYSEXEC

TSOCMD = ALLOC F(SYSPROC) DA('') SHR REUSE"
ADDRESS TSO TSOCMD

Platinum commands

2008-07-02T07:14:00.000-07:00

S COMMAND COMMAND DESCRIPTION

RC/QUERY TABLE REPORT OPTIONS
_ A - RQTA Table Alias
_ D - RQTD Table Detail
_ L - RQTL Table List
_ P - RQTP Table Plan Inquiry
_ S - RQTS Table Synonym Inquiry
_ I - RQTI Table Index Inquiry
_ C - RQTC Table Column Inquiry
_ V - RQTV Table View Inquiry
_ HL - RQTHL Table History List
_ HD - RQTHD Table Detail History
_ HS - RQTHS Table Stats. History
_ HP - RQTHP Table Partition History
_ O - RQOD Object Dependency
_ UA - RQTUA Table User Authorization Inq
_ PA - RQTPA Table Plan Authorization Inq
_ KA - RQTKA Table Package Authorization Inq
_ PK - RQTPK Table Package Inquiry
_ UC - RQTUCS Table Unique Constraint Inquiry
_ DI - RQTDI Table Drop Impact
_ LR - RQTLR Table Lob Relationships
_ TG - RQTTG Table Trigger

RC/QUERY AVAILABLE REPORT OBJECTS
_ C OPTIONS - Column
_ SY OPTIONS - System
_ RI OPTIONS - Referential Integrity
_ U OPTIONS - User

GENERAL LINE COMMANDS
_ COUNT - Display number of table rows
_ DDL - Create DDL for an object
_ DELETE - Jump to ISQL to delete from a table or view
S COMMAND COMMAND DESCRIPTION
_ DISPLAY - Jump to DB2C to display a DB/TS/IS/UDF or Stored Proc.
_ DISPLKS - Display database/tablespace/indexspace with LOCKS
_ DISPLPL - Display database/tablespace/indexspace with LPL
_ DISPUSE - Display database/tablespace/indexspace with USE
_ FSTART - Start a database and/or tablespace/indexspace
_ FSTOP - Stop a database and/or tablespace/indexspace
_ INSERT - Jump to ISQL to insert into a table or view
_ LGRANT - Grant privileges to users
_ LOAD - Create JCL for LOAD Utility
_ LREVOKE - Revoke privileges from users
_ REFRESH - Refresh Materialized Query Table
_ SELECT - Jump to ISQL to select from a table or view
_ START - Jump to DB2C to start a DB/TS/IS/UDF or Stored Proc.
_ STARO - Jump to DB2C to start a DB/TS/IS in READ ONLY status
_ STAUT - Jump to DB2C to start a DB/TS/IS in UTILITY status
_ STAFO - Jump to DB2C to start a DB/TS/IS with ACCESS FORCE
_ STOP - Jump to DB2C to stop a DB/TS/IS/UDF or Stored Proc.
_ UPDATE - Jump to ISQL to update a table or view
_ UNLOAD - Create JCL for the FAST UNLOAD utility

LICENSED LINE COMMANDS
_ ALTER - Jump to RC/Update to alter an object
_ BROWSE - Jump to RC/Update to browse a table
_ COPYD - Jump to RC/Update to copy a table
_ COPYAUTH - Jump to RC/Secure to copy authorizations
_ CREATE - Jump to RC/Update to create an object
_ DROP - Jump to RC/Update to drop an object
_ EDIT - Jump to RC/Update to edit a table
_ FBROWSE - Jump to RC/Update to browse a table
_ FEDIT - Jump to RC/Update to edit a table
_ GRANT - Jump to RC/Secure for GRANT services
_ QMIGRATE - Jump to RC/Migrator to perform Quick Migrations
_ REVOKE - Jump to RC/Secure for REVOKE services
_ TEMPLATE - Jump to RC/Update to template an object
_ LISTCAT - IDCAMS Listcat of tablespace/indexspace
_ EX - Execute Clist or REXX Exec
_ TESTEX1 - SAMPLE REXX EXEC
******************************* BOTTOM OF DATA ************

S COMMAND COMMAND DESCRIPTION

RC/QUERY SYNONYM REPORT OPTIONS
_ L - RQSL Synonym List
_ T - RQST Synonym Table Inquiry
_ V - RQSV Synonym View Inquiry
_ P - RQSP Synonym Plan Inquiry
_ PK - RQSPK Synonym Package Inquiry
_ DI - RQSDI Synonym Drop Impact

RC/QUERY AVAILABLE REPORT OBJECTS
_ T OPTIONS - Table
_ C OPTIONS - Column
_ SY OPTIONS - System
_ RI OPTIONS - Referential Integrity
_ U OPTIONS - User

picture string

2008-07-02T07:11:00.000-07:00

= any character . invalid characters
@ alphabetic characters - non-numeric characters
# numeric characters < lower case alphabetics
$ special characters > upper case alphabetics
¬ non-blank characters

FTP DOS

2008-07-02T07:09:00.000-07:00

verboseopen 159.xxx.xxx.xxuseridpwwdxxascd "'PFM.SBA.LDDNB504.D200608'";quo site xlate=canftptrquo site xlate=canftpnzquo site autorecall pr
lcd C:\SBA-AUG06
get AUDREF LDDNB504.txt
cd "'PFM.SBA.LDDNB7A.D200608'"get AUDREF LDDNB7A.txt
cd "'PFM.SBA.LDSBA504.D200608'" get AUDREF LDSBA504.txt
cd "'PFM.SBA.LDSBA7A.D200608'" get AUDREF LDSBA7A.txt
cd "'PFM.SBA.LNDNB504.D200608'" get AUDREF LNDNB504.txt
cd "'PFM.SBA.LNDNB7A.D200608'" get AUDREF LNDNB7A.txt

bye

rowid

2008-07-02T07:08:00.000-07:00

Our env: DB2 v8.1 on AIX5.3
My question is, I have a SQL statement which works fine in oracle...
update mytab set c1 = -c1 where rowid = v_rowid;
I need an equivalent in db2 (I am aware rowid equivalent is row_number() over() but I am wondering how to assign the left hand part of rowid because mytab table does not contain the column rowid!!).
Any ideas? thanks.
dr_suresh20 View Public Profile Send a private message to dr_suresh20 Send email to dr_suresh20 Find all posts by dr_suresh20
#2 08-07-06, 11:44 Peter.Vanroose Registered User Join Date: Sep 2004Location: BelgiumPosts: 602 Essentially there is no equivalent in DB2.The logic behind this being that there is no "natural" rowid attached to a certain row: a certain row can be the 4th result row from a certain query, but the same row could as well be the 6th row from the SAME query on the SAME table when the optimizer chose to implement the query differently! (E.g., with or without the use of an index.)
Typically you probably wanted to change a value in the row with rowid=4 because some (independent) program logic found out that this row had a certain property, i.e., "where" condition.In that case you could do one of three things:1. Add that condition to your UPDATE statement:update mytab set c1 = -c1 where CONDITION;2. Suppose you are running through a result table with a cursor, and at a certain iteration the condition applies for the current row; in that case useupdate mytab set c1 = -c1 where CURRENT OF cursorname;3. Use a subquery to produce the primary key values of mytab that satisfy the condition:update mytab set c1 = -c1 where mytab.pkey IN ( SELECT pkey FROM mytab WHERE CONDITION );__________________--_Peter Vanroose,__IBM Certified Database Administrator, DB2 V8 z/OS__IBM Certified Application Developer, DB2 V8__ABIS Training and Consulting__http://www.abis.be/

Every record stored has a ROWID; which is a combination of page(block)#,slot #(within a page) and file#(and optionally object#). ROWID is the fastest way of getting to a record; is used by indexes. Oracle provides it as a pseudocolumn; db2 does NOT provide rowid. Rowid is fixed,constant, immutable till you do a REORG. If you analyze some records and you want to modify or select those records again, you should store their rowids so that you can get to them most efficiently. ROWNUM is different; IN db2 rowid is 4 bytes; 3 for page# and one for slot#; one byte, 8 bits can go up to 255; that's why you have 255 record limit per page; in VIPER you have 4byte page and 2 byte slot#;so you have bigger limits on table size and # of records per page.

qmf commands

2008-07-02T07:06:00.000-07:00

LIST QUERIES

LIST TABLES

Command What it does

CONVERT Converts a prompted, QBE, or SQL query into an equivalent SQL
query. The comments in the original query do not appear in the
converted query.

DISPLAY Retrieve an object from the database and display it on your
terminal.

EDIT Edit a table in the database using the Table Editor. From the
database object list, you can only use the EDIT command to edit
a table. If you want to edit a query or procedure, you must
display it first.

ERASE Delete an object from the database.

EXIT End your QMF session.

EXPORT Export QMF objects stored in the database directly from the
database into a file (CMS), data set (TSO and CICS/MVS), or
queue name (CICS).

IMPORT Import QMF objects directly into the database from a file
(CMS), data set (TSO and CICS/MVS), or data queue name (CICS).

LAYOUT Display the format of a report produced from a given form
without using any data. You can only use LAYOUT with form
objects, and only in an environment in which both REXX and ISPF
are available.

PRINT Print a database object.

RUN Execute a query or procedure stored in the database.

SAVE Replace the object in the database with the object currently in
temporary storage. For example, if you enter:

SAVE QUERY AS

next to a query on the database object list, QMF replaces that
query in the database with the query currently in temporary
storage.

Using a Placeholder on the List of Database Objects

You can use a slash / as a placeholder to represent the object type, owner,
and name in a QMF command.

For example, entering the following in the Action area for a table object:

EDIT / (MODE=ADD

means the same as entering:

EDIT TABLE owner.tablename (MODE=ADD

where owner.tablename is the owner and name of the table listed.

You can also use /T if you just want to specify the object type, or /N if
you just want to specify the owner and name. The /T and /N placeholders
are especially useful if you are issuing a command to run a user-written
application that requires just the object type or just the object owner an
name.

If you are displaying a list from a remote location, the placeholder
symbols (/ and /N) include the location with the owner and name.

You can also display the prompt panel for a command with the object type
and the object owner and name filled in. To do this, type the command
followed by the / placeholder and a question mark.

For example, to display the RUN Command Prompt panel for the DEPTQUERY
object, enter "RUN / ?" in the Action area next to the object. The RUN
QUERY Command Prompt panels display. The first panel already has the

The placeholder options that you can enter are:

- "/T" - to display object type
- "/N" - to display owner.name
- "/" - to display both object type and owner.name

npep NCP

2008-01-16T07:53:00.000-08:00

PROCESS LOGIC NARRATIVE

Identify the NCPs participating in NPEP, whose Referral date is > 04/01/2006 regardless of the Case status and Program type is ‘W’ or ‘L’.

Read the Pram file for the dates and if it is empty
get the Batch cycle date form the Table VBATCH_CYCLE_CTRL

EXEC SQL
SELECT BATCH_CYCLE_DT
INTO :DCLVBATCH-CYCLE-CTRL.BATCH-CYCLE-DT
FROM VBATCH_CYCLE_CTRL
WITH UR
END-EXEC

Get the Batch cycle date from VBATCH_CYCLE_CTRL Table and

The Driver cursor..

EXEC SQL
DECLARE NPEP_CUR CURSOR WITH HOLD FOR
SELECT D.JURISD_CD
,D.JURISD_NAM
,A.CASE_NUM
,A.CASE_STATUS
,A.AP_IRN
,COALESCE((RTRIM(C.CL_LAST_NAME) CONCAT ', '
CONCAT RTRIM(C.CL_FIRST_NAME) CONCAT ' '
CONCAT RTRIM(C.CL_MIDDLE_NAME)),' ')
AS NCP_NAME
,B.REFERRAL_DT
,B.REFERRAL_TYPE_CD
,B.REFERRED_COUNTY
,B.PROGRAM_TYPE_CD
,B.ENROLLMENT_DT
,B.COMPLETION_DT
,B.NOT_COMPLETED_IND
FROM VCASE A
,VNPEP_REFERRAL B
,VCLIENT_ID C
,VJURISDICTION D
WHERE B.IRN > :WS-CKPT-IRN
AND A.AP_IRN = B.IRN
AND B.IRN = C.IRN
AND B.REFERRAL_DATE > '04-01-2006'
AND B.PROGRAM_TYPE_CD IN ('W','L')
AND D.JURISD_CD = B.REFERRED_COUNTY
ORDER BY B.IRN
WITH UR
END-EXEC.

EXEC SQL
OPEN NPEP_CUR
END-EXEC.

EXEC SQL
FETCH NPEP_CUR
INTO :DCLVJURISDICTION.JURISD-CD
,:DCLVJURISDICTION.JURISD-NAM
,:DCLVCASE.CASE-NUM
,:DCLVCASE.CASE-STATUS
,:DCLVCASE.AP-IRN
,:WS-NCP-NAME
,:DCLVNPEP-REFERRAL.REFERRAL-DT
,:DCLVNPEP-REFERRAL.REFERRAL-TYPE-CD
,:DCLVNPEP-REFERRAL.REFERRAL-COUNTY
,:DCLVNPEP-REFERRAL.PROGRAM-TYPE-CD
,:DCLVNPEP-REFERRAL.ENROLLMENT-DT
,:DCLVNPEP-REFERRAL.COMPLETION-DT
,:DCLVNPEP-REFERRAL.NOT-COMPLETED-IND
END-EXEC.

After successful Fetch

Get the Total Payments made since NCP is Referred, exclude Payment source
F-Federal Tax Intercept, S-State Tax Intercept, or L-Lottery Intercept.

EXEC SQL
SELECT SUM(PAYMENT)
FROM
(
SELECT COALESCE(SUM(RCPT_AMT),0) AS PAYMENT
FROM SIDPD008.VPAYMENT A
WHERE A.IRN = :DCLVCASE.AP-IRN

AND A.PYMNT_SRC NOT IN ('F','S','L')
AND A.COLLECTION_DT > '2006-04-01'

UNION ALL

SELECT COALESCE(SUM(
CASE
WHEN TRANS_TYPE = 'RP'
THEN (0 - B.APPLIED_AMT)
ELSE APPLIED_AMT
END
),0) AS PAYMENT
FROM SIDPD008.VPAYMENT_HISTORY A
,SIDPD008.VDISTRIBUTION_HIST B
WHERE A.RCPT_NUM = B.RCPT_NUM
AND A.JURISD = B.JURISD
AND DBCR_CD IN ('20','30','60','70')
AND A.PYMNT_SRC NOT IN ('F','S','L')
AND A.COLLECTION_DT > '2006-04-01'
AND A.IRN = :DCLVCASE.AP-IRN
) AS PAYTBL

END-EXEC

Get the last Payment Source

EXEC SQL
SELECT PYMNT_SRC
FROM
(SELECT PYMNT_SRC,COLLECTION_DT
FROM SIDZ1008.VPAYMENT
WHERE IRN = :DCLVCASE.AP-IRN
AND PYMNT_SRC NOT IN ('F','S','L')
AND COLLECTION_DT > '2006-11-01'

UNION ALL

SELECT PYMNT_SRC,COLLECTION_DT
FROM SIDZ1008.VPAYMENT_HISTORY
WHERE IRN = :DCLVCASE.AP-IRN
AND PYMNT_SRC NOT IN ('F','S','L')
AND COLLECTION_DT > '2006-11-01' ) AS A
ORDER BY COLLECTION_DT DESC
FETCH FIRST ROW ONLY
END-EXEC

• MOVE all the variables to output file layout

WRITE the Extract file.

After creating Extract file

EXEC SQL
CLOSE NPEP_CUR
END-EXEC.

read somewhere

2007-12-17T13:24:00.000-08:00

DB2 join challenge
--------------------------------------------------------------------------------

I have table A with 2 columns: PK [integer] and Txt [varchar(128)],
size - 1000 rows. Also, I have table B with 100000 rows and 100 integer
columns, each one is a foreign key to A's PK. I need to create a view
in which every cell of B is replaced by the Txt from A according to the
PKs as in the example below. I assume I need to perform a join, but the
SQL I used writes a huge (several GB) temporary tablespace and thus it
is very slow. Any suggestions?

B:
X Y Z ...
----------------
1 3 5
3 2 1

A:
PK Txt
-----------
1 a
2 b
3 c
5 d

Resulting view:
X Y Z ...
----------------
a c d
c b a

--------------------------------------------------------------------------------

DB2 Admin Tool - Great manager for DB2 SQL server. Get access now. Download trial!. ( www.sqlmanager.net )
SQL Server Reporting Tool - Create SQL Reports fast! Simple Drag & Drop. Free Trial.. ( sql-reports.synaptris.com )
Backup Solutions - Data Protection-Business Continuity Expert Backup and Recovery Services. ( www.DataTechBackups.com )
Ads by Google
Knut Stolze
Guest
n/a Posts December 20th, 2005
03:25 PM
#2

Re: DB2 join challenge
--------------------------------------------------------------------------------

redeck wrote:
[color=blue]
> I have table A with 2 columns: PK [integer] and Txt [varchar(128)],
> size - 1000 rows. Also, I have table B with 100000 rows and 100 integer
> columns, each one is a foreign key to A's PK. I need to create a view
> in which every cell of B is replaced by the Txt from A according to the
> PKs as in the example below. I assume I need to perform a join, but the
> SQL I used writes a huge (several GB) temporary tablespace and thus it
> is very slow. Any suggestions?
>
> B:
> X Y Z ...
> ----------------
> 1 3 5
> 3 2 1
>
> A:
> PK Txt
> -----------
> 1 a
> 2 b
> 3 c
> 5 d
>
> Resulting view:
> X Y Z ...
> ----------------
> a c d
> c b a[/color]

Well, what have you tried already?

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

--------------------------------------------------------------------------------

redeck
Guest
n/a Posts December 21st, 2005
07:15 AM
#3

Re: DB2 join challenge
--------------------------------------------------------------------------------

CREATE VIEW VIEW_1 AS SELECT T1.TXT X, T2.TXT Y, T3.TXT Z FROM B LEFT
JOIN A AS T1 ON B.X = T1.PK LEFT JOIN A AS T2 ON B.Y = T2.PK LEFT JOIN
A AS T3 ON B.Z = T3.PK

--------------------------------------------------------------------------------

Knut Stolze
Guest
n/a Posts December 21st, 2005
08:05 AM
#4

Re: DB2 join challenge
--------------------------------------------------------------------------------

redeck wrote:
[color=blue]
> CREATE VIEW VIEW_1 AS SELECT T1.TXT X, T2.TXT Y, T3.TXT Z FROM B LEFT
> JOIN A AS T1 ON B.X = T1.PK LEFT JOIN A AS T2 ON B.Y = T2.PK LEFT JOIN
> A AS T3 ON B.Z = T3.PK[/color]

Why are you using a left outer join here? If I got this right, you'll not
have the case that there are any rows in table B that are not in A, right?
So you should try the following first:

SELECT t1.txt, t2.txt, t3.txt
FROM b JOIN a AS t1 ON ( b.x = t1.pk )
JOIN a AS t2 ON ( b.y = t2.pk )
JOIN a AS t3 ON ( b.z = t3.pk )

I would prefer the following way of writing query as it makes things more
explicit. However, I would not be surprised if that gives exactly the same
plan as the query above.

SELECT ( SELECT txt FROM a WHERE a.pk = b.x ),
( SELECT txt FROM a WHERE a.pk = b.y ),
( SELECT txt FROM a WHERE a.pk = b.z ),
FROM b

If removing the outer join is not leading to the desired results, you should
verify that you have the proper indexes defined on both tables. One index
on A(PK) is needed anyways for the primary key. Three indexes on B(X),
B(Y), and B(Z) might be helpful. Additionally you could try to use an
index A(PK) and include the TXT column. That way, it might be possible to
answer the query completely with index access only.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

--------------------------------------------------------------------------------

mittalashish@gmail.com
Guest
n/a Posts December 21st, 2005
11:25 AM
#5

Re: DB2 join challenge
--------------------------------------------------------------------------------

Assuming that the table B has a primary key column, something like this
might work:

select b.pk, max(x) x, max(y) y, max(z) z ....
from (
select b.pk, case when b.x=a.pk then a.txt end x,
case when b.y=a.pk then a.txt end y,
case when b.z=a.pk then a.txt end....
from b,a where
b.x=a.pk or b.y=a.pk or b.z=a.pk....) etc.

--------------------------------------------------------------------------------

redeck
Guest
n/a Posts December 22nd, 2005
04:15 PM
#6

Re: DB2 join challenge
--------------------------------------------------------------------------------

Without the LEFT modifier I am getting the SQL0101N error: The
statement is too long or too complex. This is very strange because I
have STMTHEAP = 32768 x 4KB pages = 128 MB.

--------------------------------------------------------------------------------

Brian Tkatch
Guest
n/a Posts December 22nd, 2005
04:35 PM
#7

Re: DB2 join challenge
--------------------------------------------------------------------------------

This is just a wild guess.

Is it possible to split the query into smaller parts without LEFT, to
avoid the to complex error, and then use another view to UNION ALL them
together.

B.