Download SELCOPY Manual

DB2 Processing
DB2 Example 5: Output file
Positions of columns and indicator variables are equated as offsets on XX which needs to be equated to the start of the required
I/O area by the user.
DB2 Example 5: Output file
*
--- SELCOPY EQU stmts gen'd by SSDB2EQU --2001/11/26 16:52
o * SELECT *
*
FROM
*
DSN8410.EMP
o
* Extnl Intnl
Indicator
* Len
Len
Data Type
Var
* ----------------o equ EMPNO
XX+00000 *
??
6 CHAR
equ FIRSTNME
XX+00008 *
??
12 VARCHAR
equ MIDINIT
XX+00020 *
??
1 CHAR
o equ LASTNAME
XX+00023 *
??
15 VARCHAR
equ WORKDEPT
XX+00040 *
??
3 CHAR
NULLS
equ PHONENO
XX+00045 *
??
4 CHAR
NULLS
o equ HIREDATE
XX+00051 *
??
10 DATE
NULLS
equ JOB
XX+00063 *
??
8 CHAR
NULLS
equ EDLEVEL
XX+00073 *
??
2 SMALLINT
NULLS
o equ SEX
XX+00081 *
??
1 CHAR
NULLS
equ BIRTHDATE
XX+00084 *
??
10 DATE
NULLS
equ SALARY
XX+00096 *
??
5 DECIMAL(09,02)
NULLS
o equ BONUS
XX+00109 *
??
5 DECIMAL(09,02)
NULLS
equ COMM
XX+00122 *
??
5 DECIMAL(09,02)
NULLS
o
o
o
o
o
o
o
Ctl Cards for generating LOAD statements
A second SELCOPY control card file, SSDB2LD CTL is supplied on the Distribution Master for generating control statements for
the DB2 LOAD Utility.
This is a faster method of loading a table than using INSERT statements via SQL, because the LOAD Utility interfaces directly with
DB2.
Direct READ of DB2 Tables
DB2 provides the SELECT INTO SQL statement which allows the reading of a results table, containing at most one row, without
having to OPEN a SELECT cursor, FETCH the row and CLOSE the SELECT cursor.
This enables applications to do direct reads of DB2 tables similar to VSAM Keyed Reads or IMS Get Uniques.
Unfortunately, the SELECT INTO SQL statement cannot be dynamically prepared. This is a DB2 restriction (at least in all DB2
releases through to 5.1).
Since SELCOPY uses dynamic SQL, the SELECT INTO statement is not available to SELCOPY DB2 users. Direct reads can be
done with SELCOPY but only by using OPEN, FETCH and CLOSE. The overheads of the OPEN and CLOSE make this processing
much less efficient than a corresponding static SQL application using SELECT INTO.
For example, the following direct read of the EMP table using static SQL:
SELECT * INTO :EMPREC FROM DSN8410.EMP WHERE EMPNO='528671'
must be implemented in SELCOPY as:
rd
emprec
close emprec
SQL="SELECT * FROM DSN8410.EMP WHERE EMPNO='528671'" into 101
* Explicit CLOSE required since EOF not reached.
SELCOPY SQL Error Handling
See also:
• POS RPL and POS SQLCA in section Operation Words, Parameters and Keywords.
Various sorts of error can occur when trying to process SQL data with SELCOPY:
• DB2 connection errors.
• SQL OPEN errors.
• SQL EXECUTE errors (the SELCOPY DB2 operation)
• SQL FETCH (READ), UPDATE, INSERT and DELETE errors.
The component of SELCOPY which manages the access to DB2 data maintains the user information area at POS RPL. This area
contains various return and reason codes describing the status of the most recent DB2 action.
2014-08-07 10:16:24
SELCOPY Manual
277