- GPLAN --- proper usage
- Do you need the plan = parameter?
- Can I dump DB2 table information to a file using a SQL
statement?
- What causes a -805?
- What's the deal with the -905?
- Why is my program running so long?
- Common error messages
GPLAN is only valid in production (DBIXEQ only). It is used so anyone can
submit a production program (one which is not DB2 but calls a common access DB2
program such as AR2861).
Back to Top
You do not need to have the plan = parameter at all since we switched
to packages. Although the PLAN parameter is no longer valid, if included it will
be ignored and will not produce an error.
Back to Top
This is a utility that can be used to dump a table (select *) or
any SQL statement to a vsam file. A sample is in 'UIDP.PROD.JCL(JL0030DB)'
See below:
//JL0030DB JOB (001,0030,000),'BRENDA ULIN',CLASS=O,MSGCLASS=Y
//UNCATLG EXEC PGM=IEFBR14
//UNCAT1 DD DSN=DISK.D0030.JL.ADMCFEES.DATA,
// UNIT=DISK,SPACE=(TRK,(1)),
// DISP=(MOD,DELETE,DELETE)
//UNCAT2 DD DSN=WEEKLY.&SYSUID..SQLCMDS.ADMCFEES,
// UNIT=DISK,SPACE=(TRK,(1)),
// DISP=(MOD,DELETE,DELETE)
//UNCAT3 DD DSN=WEEKLY.&SYSUID..SQLRECS.ADMCFEES,
// UNIT=DISK,SPACE=(TRK,(1)),
// DISP=(MOD,DELETE,DELETE)
//*
//UNLOAD EXEC SQLUNLOD,SYSID=DSN,SUFFIX=ADMCFEES
//SQLUNLOD.SYSIN DD *
SELECT A.ADMC_SOC_SEC#, CHAR(DECIMAL(ADMC_SEQ#,5,0)),
ADMC_AREA, ADMC_CATEGORY, CHAR(ADMC_FEE_AMOUNT),
ADMC_AMOUNT_TYPE, ADMC_ACTION, ADMC_INPUT_DATE,
ADMC_DESC, ADMC_COLLEGE, ADMC_SESSION,
ADMC_FEES_USERID
FROM ADPD004.ADMCMSTR A, ADPD004.ADMCFEES B
WHERE A.ADMC_CONTROL# = B.ADMC_CONTROL#;
/*
//* This is just to rename the dataset
//SORT1 EXEC SORT
//SORTIN DD DSN=WEEKLY.&SYSUID..SQLRECS.ADMCFEES,
// DISP=SHR
//SORTOUT DD DSN=DISK.D0030.JL.ADMCFEES.DATA,
// SPACE=(10790,(5000,10),RLSE),
// DISP=(NEW,CATLG,DELETE),UNIT=DISK,DCB=*.SORTIN
//SYSIN DD *
SORT FIELDS=COPY
END
/*
Back to Top
The most common reason a -805 occures is when the timestamp for the DB2 DBRM
(SQL) and the timestamp for the PL/1 loadlib member are not the same. This
usually happens because the program has "linked"(new loadlib member)
but there is an SQL error in the BIND step of the compilation process.
You can also get it if someone recomplies a member of the BTCHCOL2 or
if it someone uses the gplan parameter but the program is not in the BTCHCOL2
package
Back to Top
In DB2, a -904 is a "Resource Not Available". There could be
several reasons for this, but usually it is one of three:
1. An image copy needs to be taken. DB2 turns the copy pending flag on if the
DB2 load utility has been run with the parameter "log no". Since
"log no" does not log transactions to the system log, DB2 requires an
image copy be performed. This gives DB2 a starting place in case recovery needs
to be run at some later time. If an image copy was not taken, the rows inserted
with the load utility (with "log no") would be lost.
2. Contention with another program. DB2 allows concurrency. This means more
than one process can update a table at the same time. But long running update
programs can change this. If an update program is started, every time an update
happens a lock is placed on the CI (or row if row level locking is being used).
DB2 only has a certain amount of locks. Once this maximum threshold of locks is
used, DB2 escalates to a tablespace lock. This means only the process that
caused the escalation can use the table. All other processes will receive a
-904.
3. A DB2 utility is being executed. Certain DB2 utilities require exclusive
control of the table. If a process is started while one of these is executing, a
-904 is issued.
Back to Top
There can be hundreds of reasons why a program is taking a long time to run.
What we will attempt to do here is document the most common reasons.
1. An index is not being used. Running the "EXPLAIN" utility will
show whether or not and index is being used for a particular SQL statement. If
one exists but is not being used, maybe a reorg. of the table is needed. If the
cluster ratio of the index is low (less than 95%), DB2's optimizer may elect to
use a table scan rather than the index. One way of testing this before doing a
reorg. is to use Platinum's PDA and change the cluster ratio to 100%, rebind the
package, and run the EXPLAIN utility to see if DB2's optimizer chooses the
index.
2. A massive delete has occurred. Even though a process has deleted several
rows of data, if a reorg. has not been done afterwards, those rows still
physically exist in the table. DB2 has to check a delete indicator to see if the
row should be included in processing. Only a reorg. will physically delete these
rows.
3. Processing two different data sources that are ordered differently. Here
is a typical example:
A program is using a VSAM file that has its primary key of social security
number in terminal digit (TD) order and is using a DB2 table ordered by social
security number in straight order. If, for example, the "trigger" file
is the DB2 table, for every access made to the VSAM file an I/O might have to
occur. VSAM will be jumping all over the file because processing is not by the
order of the data.
Back to Top
UNDEFINED OR UNUSABLE HOST
VARIABLE "variable_name"
This error message usually follows a:
IEF450I TB0015 PRINT SQLTRAN - ABEND=S000
U0116 REASON=000000
One possible cause of this is the two-level
limitation for qualified references to PL/I data
structure variables.
Using a host
structure you can qualify a host variable with the structure name. However
host structures are limited to two levels. Qualifying a host variable with the
higher level name and going to 3 or more levels can cause this error message.
See
DB2 for MVS/ESA V4 Application Programming and SQL Guide
(Document number SC26-3266-00) section 3.4.5.6 "Using
Host Structures."
Back to Top
