By default, DB2 uses the following values for primary space allocation of DB2–managed data sets:
1 cylinder (720 KB) for non-LOB table spaces
10 cylinders for LOB table spaces
1 cylinder for indexes
How to calculate the size of Index:
I had been using Approach 1 across various DB2 UDB projects , other alternates are listed below Approach 1 ------------ Index Space Size = (average index key size + 8) * no of rows * 2 Approach 2 ------------ Index size estimation : Avg Sum of column widths * Expected no of rows
Approach 3 ------------- Use Control center
I've planned to use the following steps to determine the DMS tablespace size. Pls correct me if I'm wrong else pls give your ack.
Think Scenario:For a single table I've deceided to use a seperate tablespaces for Index & Data. hence
Table Size = Avg Byte count per Row * No. of rows
Avg Byte count per Row is derived using this query after runing the runstats against that table.
SELECT sum(AVGCOLLEN) FROM SYSCAT.COLUMNS where tabname='TestTable'
Hence in my case the mathematics involved is as follows..
Table Size = Avg Byte count per Row * No. of rows= 262 * 132800000
= 34793600000 bytes= 32.40406513214 GB
Can you please let me know whether this inference is correct ? Any suggestions highly appreciated.
DB2 Space ManagementObjects are stored in data files which are logically grouped in tablespaces. If you choose Database Managed Tablespace(DMS), then when the size you allocated to associated data files is over, you have to increase by desired amount. If it is System Managed Tablespace(SMS), then DB2 every time data files space is over, additional extents are allocated. This repeated allocation & OS checks lead to less performance.
The smallest unit of database storage is page whose sizes are like 4K, 8K, 16K, 32K etc. A number of these are contained in an OS block.
In windows when the drive closes & in UNIX the max folder/file size is exceeded, functioning stops till you clear some space.
In RAM, buffer pools exist having buffer size which should be same as page size of tablespace to which this bufferpool is assigned.
Only table & index need space for data else for all objects only definition is stored for which very less space is required.
Table, index & temporary tablespace sizes are calculated as follows:
Table size = ((Average row size * number of rows) + 8) * 1.5
Index Size = ((Average key size * number of rows) + 8) * 2
Temporary tablespace size = ((Average key size * number of rows) + 8) * 3.2
The unit will be bytes.Average row/key size for table/index can be got by right click on given table/index and choosing estimate size option.
The other way is total the bytes of all columns keeping 75% of varchar columns.
For temporary tablespace, group function columns like max, avg etc., group by and order by have to be considered since sorting is involved.
Apply the formula for each such set. 1.5, 2 & 3.2 are buffer multipliers.
Along with size, growth also needs to be estimated initially on project basis & later based on actual results.
To store objects & data which will be read only one can use SMS. But for growing database i.e. tablespaces i.e. datafiles i.e. bufferpools i.e. buffers, one has to select a DMS tablespace with optimum size balancing between maintainence & performance.
ReplicationYes, DB2 supports both synchronous & asynchronous and uni & bi-directional replication.
Synchronous means both source & target will be continuously in sync where as asynchronous implies periodaically.
Uni means one is source & the other target.
Bi means both are source & both are target.
SQL CompliantDB2 obeys/follows ANSI SQL 99 standards.User ManagementWhat ghp7000 said is true about this. DB2 can have only OS users & no database users. Hence number of users allowed by OS can become a limit. And off course the resources that are allocated to a OS user like part of RAM can decided the upper limit for their count. You can check Appendix G i.e. SQL limits in SQL Reference for the OS & DB2 type & version obtainable as a PDF from IBM DB2 site.
For estimating index and tablespace size in db2 i used the formula ------------------------------------------------------------------------------
1) Average Row Size: The average row size is the sum of the average column sizes
2) For each user table in the database, the number of 4 KB pages can be estimated by calculating: ROUND DOWN(4020/(average row size + 10)) = records_per_page
3) Then use records_per_page with: (number_of_records/records_per_page) * 1.1 = number_of_pagesThe factor of "1.1" is for overhead
4)For each index, the space needed can be estimated as: (average index key size + 9) * number of rows * 2
Wednesday, July 2, 2008
Tuesday, July 1, 2008
DSNUPROC - Invocation of DB2 utility by using the supplied JCL procedure
First of all,
What is a utility versus other popular terms such as “tool” and “solution.”
A utility is a single purpose batch program for moving and/or verifying database pages; examples include LOAD, UNLOAD, REORG, CHECK, COPY, and RECOVER.
A database tool is a multi-functioned program designed to simplify database monitoring, management, and/or administration tasks.
A solution is a synergistic group of tools and utilities designed to work together to address a customer’s business issue.
DSNUPROC is a JCL Supplied procedure to invoke DB2 Online Utilities.
This procedure uses the parameters that you supply to build an appropriate EXEC statement that executes an online utility.To execute the DSNUPROC procedure, write and submit a JCL data set .In your JCL, the EXEC statement executes the DSNUPROC procedure
At a minimum you will need utilities:
to backup your data
to recover and restore your backups
to reorganize inefficient database objects
to collect database object statistics
to manipulate statistics in the DB2 Catalog
to load and unload data
to check for data integrity problems
Currently IBM only offers one choice to its customers: buy all of the DB2 utilities or none of them. So, DB2 shops have to decide whether they will buy and run IBM’s utilities, or use a competing set of utilities. The ISVs offer more flexibility, though. For example, you could buy your REORG and UNLOAD from BMC, your CHECK and COPY from Computer Associates, your LOAD from CDB, etc. It is not an all-or-nothing proposition. But you will need to make sure you have the entire suite of utility functionality covered.
Sample DSNUPROC :
//STEP1 EXEC DSNUPROC,UID='SMPLUNLD',UTPROC='',SYSTEM='DB8A'
//STEPLIB DD DSN=DB8A.DSN810.SDSNLOAD,DISP=SHR
//SYSREC DD DSN=TSOBZMA.#TS#.UNLOAD,
// DISP=(NEW,CATLG,DELETE),
// UNIT=SYSDA,SPACE=(TRK,(2,1))
//SYSPUNCH DD DSN=TSOBZMA.LDCARD(#TS#),DISP=SHR
//SYSPRINT DD SYSOUT=*
//SYSIN DD *
UNLOAD TABLESPACE #SCHEMA#.#TS#
DELIMITED CHARDEL '#' COLDEL ';' DECPT '!'
FROM TABLE TSOTZMA.FRAUDACCTSUM
/*
Sample listing of supplied JCL procedure DSNUPROC
//DSNUPROC PROC LIB='DSN!!0.SDSNLOAD',
// SYSTEM=DSN,
// SIZE=0K,UID='',UTPROC=''
//********************************************************************
//* PROCEDURE-NAME DSNUPROC
//* *
//* DESCRIPTIVE-NAME: UTILITY PROCEDURE *
//* *
//* FUNCTION: THIS PROCEDURE INVOKES THE ADMF UTILITIES IN THE *
//* BATCH ENVIRONMENT *
//* *
//* PROCEDURE-OWNER: UTILITY COMPONENT *
//* *
//* COMPONENT-INVOKED: ADMF UTILITIES (ENTRY POINT DSNUTILB). *
//* *
//* ENVIRONMENT: BATCH *
//* *
//* INPUT: *
//* PARAMETERS: *
//* LIB = THE DATA SET NAME OF THE DB2 PROGRAM LIBRARY. *
//* THE DEFAULT LIBRARY NAME IS PREFIX.SDSNLOAD, *
//* WITH PREFIX SET DURING INSTALLATION. *
//* SIZE = THE REGION SIZE OF THE UTILITIES EXECUTION AREA.*
//* THE DEFAULT REGION SIZE IS 2048K. *
//* SYSTEM = THE SUBSYSTEM NAME USED TO IDENTIFY THIS JOB *
//* TO DB2. THE DEFAULT IS "DSN". *
//* UID = THE IDENTIFIER WHICH WILL DEFINE THIS UTILITY *
//* JOB TO DB2. IF THE PARAMETER IS DEFAULTED OR *
//* SET TO A NULL STRING, THE UTILITY FUNCTION WILL *
//* USE ITS DEFAULT, USERID.JOBNAME. EACH UTILITY *
//* WHICH HAS STARTED AND IS NOT YET TERMINATED *
//* (MAY NOT BE RUNNING) MUST HAVE A UNIQUE UID. *
//* UTPROC = AN OPTIONAL INDICATOR USED TO DETERMINE WHETHER *
//* THE USER WISHES TO INITIALLY START THE REQUESTED*
//* UTILITY OR TO RESTART A PREVIOUS EXECUTION OF *
//* THE UTILITY. IF OMITTED, THE UTILITY WILL *
//* BE INITIALLY STARTED. OTHERWISE, THE UTILITY *
//* WILL BE RESTARTED BY ENTERING THE FOLLOWING *
//* VALUES: *
//* RESTART(PHASE) = RESTART THE UTILITY AT THE *
//* BEGINNING OF THE PHASE EXECUTED *
//* LAST. *
//* RESTART = RESTART THE UTILITY AT THE LAST *
//* OR CURRENT COMMIT POINT. *
//* *
//* OUTPUT: NONE. *
//* *
//* EXTERNAL-REFERENCES: NONE. *
//* *
//* CHANGE-ACTIVITY: *
//* *
//**********************************************************************
//DSNUPROC EXEC PGM=DSNUTILB,REGION=&SIZE,
// PARM='&SYSTEM,&UID,&UTPROC' //STEPLIB DD DSN=&LIB,DISP=SHR
//**********************************************************************
//* *
//* THE FOLLOWING DEFINE THE UTILITIES' PRINT DATA SETS *
//* *
//**********************************************************************
//* //SYSPRINT DD SYSOUT=*
//UTPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//*DSNUPROC PEND REMOVE * FOR USE AS INSTREAM PROCEDURE
What is a utility versus other popular terms such as “tool” and “solution.”
A utility is a single purpose batch program for moving and/or verifying database pages; examples include LOAD, UNLOAD, REORG, CHECK, COPY, and RECOVER.
A database tool is a multi-functioned program designed to simplify database monitoring, management, and/or administration tasks.
A solution is a synergistic group of tools and utilities designed to work together to address a customer’s business issue.
DSNUPROC is a JCL Supplied procedure to invoke DB2 Online Utilities.
This procedure uses the parameters that you supply to build an appropriate EXEC statement that executes an online utility.To execute the DSNUPROC procedure, write and submit a JCL data set .In your JCL, the EXEC statement executes the DSNUPROC procedure
At a minimum you will need utilities:
to backup your data
to recover and restore your backups
to reorganize inefficient database objects
to collect database object statistics
to manipulate statistics in the DB2 Catalog
to load and unload data
to check for data integrity problems
Currently IBM only offers one choice to its customers: buy all of the DB2 utilities or none of them. So, DB2 shops have to decide whether they will buy and run IBM’s utilities, or use a competing set of utilities. The ISVs offer more flexibility, though. For example, you could buy your REORG and UNLOAD from BMC, your CHECK and COPY from Computer Associates, your LOAD from CDB, etc. It is not an all-or-nothing proposition. But you will need to make sure you have the entire suite of utility functionality covered.
Sample DSNUPROC :
//STEP1 EXEC DSNUPROC,UID='SMPLUNLD',UTPROC='',SYSTEM='DB8A'
//STEPLIB DD DSN=DB8A.DSN810.SDSNLOAD,DISP=SHR
//SYSREC DD DSN=TSOBZMA.#TS#.UNLOAD,
// DISP=(NEW,CATLG,DELETE),
// UNIT=SYSDA,SPACE=(TRK,(2,1))
//SYSPUNCH DD DSN=TSOBZMA.LDCARD(#TS#),DISP=SHR
//SYSPRINT DD SYSOUT=*
//SYSIN DD *
UNLOAD TABLESPACE #SCHEMA#.#TS#
DELIMITED CHARDEL '#' COLDEL ';' DECPT '!'
FROM TABLE TSOTZMA.FRAUDACCTSUM
/*
Sample listing of supplied JCL procedure DSNUPROC
//DSNUPROC PROC LIB='DSN!!0.SDSNLOAD',
// SYSTEM=DSN,
// SIZE=0K,UID='',UTPROC=''
//********************************************************************
//* PROCEDURE-NAME DSNUPROC
//* *
//* DESCRIPTIVE-NAME: UTILITY PROCEDURE *
//* *
//* FUNCTION: THIS PROCEDURE INVOKES THE ADMF UTILITIES IN THE *
//* BATCH ENVIRONMENT *
//* *
//* PROCEDURE-OWNER: UTILITY COMPONENT *
//* *
//* COMPONENT-INVOKED: ADMF UTILITIES (ENTRY POINT DSNUTILB). *
//* *
//* ENVIRONMENT: BATCH *
//* *
//* INPUT: *
//* PARAMETERS: *
//* LIB = THE DATA SET NAME OF THE DB2 PROGRAM LIBRARY. *
//* THE DEFAULT LIBRARY NAME IS PREFIX.SDSNLOAD, *
//* WITH PREFIX SET DURING INSTALLATION. *
//* SIZE = THE REGION SIZE OF THE UTILITIES EXECUTION AREA.*
//* THE DEFAULT REGION SIZE IS 2048K. *
//* SYSTEM = THE SUBSYSTEM NAME USED TO IDENTIFY THIS JOB *
//* TO DB2. THE DEFAULT IS "DSN". *
//* UID = THE IDENTIFIER WHICH WILL DEFINE THIS UTILITY *
//* JOB TO DB2. IF THE PARAMETER IS DEFAULTED OR *
//* SET TO A NULL STRING, THE UTILITY FUNCTION WILL *
//* USE ITS DEFAULT, USERID.JOBNAME. EACH UTILITY *
//* WHICH HAS STARTED AND IS NOT YET TERMINATED *
//* (MAY NOT BE RUNNING) MUST HAVE A UNIQUE UID. *
//* UTPROC = AN OPTIONAL INDICATOR USED TO DETERMINE WHETHER *
//* THE USER WISHES TO INITIALLY START THE REQUESTED*
//* UTILITY OR TO RESTART A PREVIOUS EXECUTION OF *
//* THE UTILITY. IF OMITTED, THE UTILITY WILL *
//* BE INITIALLY STARTED. OTHERWISE, THE UTILITY *
//* WILL BE RESTARTED BY ENTERING THE FOLLOWING *
//* VALUES: *
//* RESTART(PHASE) = RESTART THE UTILITY AT THE *
//* BEGINNING OF THE PHASE EXECUTED *
//* LAST. *
//* RESTART = RESTART THE UTILITY AT THE LAST *
//* OR CURRENT COMMIT POINT. *
//* *
//* OUTPUT: NONE. *
//* *
//* EXTERNAL-REFERENCES: NONE. *
//* *
//* CHANGE-ACTIVITY: *
//* *
//**********************************************************************
//DSNUPROC EXEC PGM=DSNUTILB,REGION=&SIZE,
// PARM='&SYSTEM,&UID,&UTPROC' //STEPLIB DD DSN=&LIB,DISP=SHR
//**********************************************************************
//* *
//* THE FOLLOWING DEFINE THE UTILITIES' PRINT DATA SETS *
//* *
//**********************************************************************
//* //SYSPRINT DD SYSOUT=*
//UTPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//*DSNUPROC PEND REMOVE * FOR USE AS INSTREAM PROCEDURE
DSNTEP2 and DSNTEP4 - DB2 Batch spufi
DSNTEP4 is an application program that can be used to issue DB2 dynamic SQL statements. It is sometimes referred to as “Batch SPUFI” because it allows you to submit SQL in batch similar to how SPUFI allows online SQL execution.
A sample dynamic SQL program that is written in the PL/I language. This program is identical to DSNTEP2 except DSNTEP4 uses multi-row fetch for increased performance. You can use the source version of DSNTEP4 and modify it to meet your needs, or, if you do not have a PL/I compiler at your installation, you can use the object code version of DSNTEP4
The following sample JCL demonstrates the capability of DSNTEP2 to issue DCL, DDL, and DML dynamically.
//DB2JOBU JOB (BATCHSQL),'DB2 SAMPLE SQL',MSGCLASS=X,
// CLASS=X,NOTIFY=USER
//*//***********************************//*
//* DB2 SAMPLE SQL PROGRAM//*
//***********************************//*
//JOBLIB DD DSN=DSN810.DSNLOAD,DISP=SHR
//BATCHSQL EXEC PGM=IKJEFT01,DYNAMNBR=20
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DSN)
RUN PROGRAM(DSNTEP2) PLAN(DSNTEP81) -
LIB('DSN810.RUNLIB.LOAD')
END
/*
//SYSIN DD *
SELECT * FROM SYSIBM.SYSTABLES;
/*
The DNSTEP2 program is written in the PL/I programming language.
Prior to DB2 V6, you needed to have a PL/I compiler to use DSNTEP2. However, as of V6 IBM now provides both the source code and an object code version of DSNTEP2 with DB2. So, you no longer need a PL/I compiler to use DSNTEP2.Because DSNTEP2 is an application program, and the PL/I source code is provided with DB2, a knowledgeable PL/I programmer can easily modify the code. After doing so, of course, it must be compiled, linked, and bound before it can be used.DSNTEP2 can process almost every SQL statement that can be executed dynamically. DSNTEP2 accepts· The GRANT and REVOKE DCL statements,· The ALTER, COMMENT ON, CREATE, and DROP DDL statements,· The DELETE, INSERT, SELECT, and UPDATE DML statements, and· The COMMIT, ROLLBACK, EXEC SQL, EXPLAIN, and LOCK statements.The only important statement that DSNTEP2 does not support is the LABEL ON DDL statement. Of course, DSNTEP2 can be modified to support this statement if you have PL/I knowledge and a PL/I compiler.
When Does DSNTEP2 Commit?
Well, the simple answer to that question is that the results of the SQL are committed upon completion of all the SQL. A helpful answer is a little longer. First off, you need to know that DSNTEP2 has an internal parameter named MAXERRORS that controls the number of failing statements that can occur before it stops. A failing statement is one which returns a negative SQLCODE. The value of MAXERRORS is set to 10 inside the program, so DSNTEP2 will allow 9 failing SQL statements but when it hits the 10th failing statement, it will exit, COMMITting all other work.This is ugly because it can wreak havoc on the integrity of your data. I mean, who wants to figure out what was run, what was impacted, and then try to rebuild a job to fix data and/or restart at the right place? To rerun DSNTEP2, remember that all SQL statements that completed with a 0 SQL code were committed. These statements should not be rerun. All SQL statements completed with a negative SQL code must be corrected and reprocessed.Certain severe errors cause DSNTEP2 to exit immediately. One severe error is a -101 “SQL statement too long or too complex".If any SQL errors occurred during the execution of DSNTEP2, a return code of 8 is returned by the job step.At any rate, DSNTEP2 never issues an explicit COMMIT or ROLLBACK by itself. A COMMIT occurs at the end unless the program abends.
Specify Your SQL Statements Properly
The SQL to be run by DSNTEP2 is specified in SYSIN. Be sure to code the DSNTEP2 input properly.DSNTEP2 reads SQL statements from an input data set with 80-byte records. The SQL statements must be coded in the first 72 bytes of each input record. SQL statements can span multiple input records and are terminated by a semicolon (;). Semicolons are not permitted in the text of the SQL statement.
Liberally Comment DSNTEP2
InputComments can be passed to DSNTEP2 in the SQL statements using two hyphens in columns 1 and 2 or a single asterisk in column 1. Doing so is good form and helps others to understand what your DSNTEP2 job is attempting to accomplish.
Bottom Line
DSNTEP2 is especially useful for running one-off SQL statements. Use DSNTEP2 when you have ad hoc SQL to run in a batch environment. DSNTEP2 is easier than writing your own quick and dirty programs to run ad hoc SQL in batch. It is simple to set up and saves time. But be careful if you have multiple SQL modification statements (INSERT, UPDATE, DELETE) because of the above-mentioned possibility of causing data integrity problems if some of the statements fail and others succeed
A sample dynamic SQL program that is written in the PL/I language. This program is identical to DSNTEP2 except DSNTEP4 uses multi-row fetch for increased performance. You can use the source version of DSNTEP4 and modify it to meet your needs, or, if you do not have a PL/I compiler at your installation, you can use the object code version of DSNTEP4
The following sample JCL demonstrates the capability of DSNTEP2 to issue DCL, DDL, and DML dynamically.
//DB2JOBU JOB (BATCHSQL),'DB2 SAMPLE SQL',MSGCLASS=X,
// CLASS=X,NOTIFY=USER
//*//***********************************//*
//* DB2 SAMPLE SQL PROGRAM//*
//***********************************//*
//JOBLIB DD DSN=DSN810.DSNLOAD,DISP=SHR
//BATCHSQL EXEC PGM=IKJEFT01,DYNAMNBR=20
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DSN)
RUN PROGRAM(DSNTEP2) PLAN(DSNTEP81) -
LIB('DSN810.RUNLIB.LOAD')
END
/*
//SYSIN DD *
SELECT * FROM SYSIBM.SYSTABLES;
/*
The DNSTEP2 program is written in the PL/I programming language.
Prior to DB2 V6, you needed to have a PL/I compiler to use DSNTEP2. However, as of V6 IBM now provides both the source code and an object code version of DSNTEP2 with DB2. So, you no longer need a PL/I compiler to use DSNTEP2.Because DSNTEP2 is an application program, and the PL/I source code is provided with DB2, a knowledgeable PL/I programmer can easily modify the code. After doing so, of course, it must be compiled, linked, and bound before it can be used.DSNTEP2 can process almost every SQL statement that can be executed dynamically. DSNTEP2 accepts· The GRANT and REVOKE DCL statements,· The ALTER, COMMENT ON, CREATE, and DROP DDL statements,· The DELETE, INSERT, SELECT, and UPDATE DML statements, and· The COMMIT, ROLLBACK, EXEC SQL, EXPLAIN, and LOCK statements.The only important statement that DSNTEP2 does not support is the LABEL ON DDL statement. Of course, DSNTEP2 can be modified to support this statement if you have PL/I knowledge and a PL/I compiler.
When Does DSNTEP2 Commit?
Well, the simple answer to that question is that the results of the SQL are committed upon completion of all the SQL. A helpful answer is a little longer. First off, you need to know that DSNTEP2 has an internal parameter named MAXERRORS that controls the number of failing statements that can occur before it stops. A failing statement is one which returns a negative SQLCODE. The value of MAXERRORS is set to 10 inside the program, so DSNTEP2 will allow 9 failing SQL statements but when it hits the 10th failing statement, it will exit, COMMITting all other work.This is ugly because it can wreak havoc on the integrity of your data. I mean, who wants to figure out what was run, what was impacted, and then try to rebuild a job to fix data and/or restart at the right place? To rerun DSNTEP2, remember that all SQL statements that completed with a 0 SQL code were committed. These statements should not be rerun. All SQL statements completed with a negative SQL code must be corrected and reprocessed.Certain severe errors cause DSNTEP2 to exit immediately. One severe error is a -101 “SQL statement too long or too complex".If any SQL errors occurred during the execution of DSNTEP2, a return code of 8 is returned by the job step.At any rate, DSNTEP2 never issues an explicit COMMIT or ROLLBACK by itself. A COMMIT occurs at the end unless the program abends.
Specify Your SQL Statements Properly
The SQL to be run by DSNTEP2 is specified in SYSIN. Be sure to code the DSNTEP2 input properly.DSNTEP2 reads SQL statements from an input data set with 80-byte records. The SQL statements must be coded in the first 72 bytes of each input record. SQL statements can span multiple input records and are terminated by a semicolon (;). Semicolons are not permitted in the text of the SQL statement.
Liberally Comment DSNTEP2
InputComments can be passed to DSNTEP2 in the SQL statements using two hyphens in columns 1 and 2 or a single asterisk in column 1. Doing so is good form and helps others to understand what your DSNTEP2 job is attempting to accomplish.
Bottom Line
DSNTEP2 is especially useful for running one-off SQL statements. Use DSNTEP2 when you have ad hoc SQL to run in a batch environment. DSNTEP2 is easier than writing your own quick and dirty programs to run ad hoc SQL in batch. It is simple to set up and saves time. But be careful if you have multiple SQL modification statements (INSERT, UPDATE, DELETE) because of the above-mentioned possibility of causing data integrity problems if some of the statements fail and others succeed
DSNTIAUL - DB2 Unload program
The sample unload program. This program, which is written in assembler language, is a simple alternative to the UNLOAD utility. It unloads some or all rows from up to 100 DB2 tables. With DSNTIAUL, you can unload data of any DB2 built-in data type or distinct type. You can unload up to 32 KB of data from a LOB column. DSNTIAUL unloads the rows in a form that is compatible with the LOAD utility and generates utility control statements for LOAD. DSNTIAUL also lets you execute any SQL non-SELECT statement that can be executed dynamically
DSNTIAUL parameters:
SQL : Specify SQL to indicate that your input data set contains one or more complete SQL statements, each of which ends with a semicolon. You can include any SQL statement that can be executed dynamically in your input data set. In addition, you can include the static SQL statements CONNECT, SET CONNECTION, or RELEASE. DSNTIAUL uses the SELECT statements to determine which tables to unload and dynamically executes all other statements except CONNECT, SET CONNECTION, and RELEASE. DSNTIAUL executes CONNECT, SET CONNECTION, and RELEASE statically to connect to remote locations.
number of rows per fetch
Specify a number from 1 to 32767 to specify the number of rows per fetch that DSNTIAUL retrieves. If you do not specify this number, DSNTIAUL retrieves 100 rows per fetch. This parameter can be specified with the SQL parameter.
#Specify 1 to retrieve data from a remote site when DSNTIAUL #is bound with the DBPROTOCOL(PRIVATE) option.
#TOLWARN
#Specify NO (the default) or YES to indicate whether DSNTIAUL continues #to retrieve rows after receiving an SQL warning: #
#
NO #
If a warning occurs when DSNTIAUL executes an OPEN or FETCH to retrieve #rows, DSNTIAUL stops retrieving rows. If the SQLWARN1, SQLWARN2, SQLWARN6, #or SQLWARN7 flag is set when DSNTIAUL executes a FETCH to retrieve rows, DSNTIAUL #continues to retrieve rows. #
Exception:# #
YES #
If a warning occurs when DSNTIAUL executes an OPEN or FETCH to retrieve #rows, DSNTIAUL continues to retrieve rows. # #
If you do not specify the SQL parameter, your input data set must contain one or more single-line statements (without a semicolon) that use the following syntax: table or view name [WHERE conditions] [ORDER BY columns]
Each input statement must be a valid SQL SELECT statement with the clause SELECT * FROM omitted and with no ending semicolon. DSNTIAUL generates a SELECT statement for each input statement by appending your input line to SELECT * FROM, then uses the result to determine which tables to unload. For this input format, the text for each table specification can be a maximum of 72 bytes and must not span multiple lines.
You can use the input statements to specify SELECT statements that join two or more tables or select specific columns from a table. If you specify columns, you need to modify the LOAD statement that DSNTIAUL generates.
DSNTIAUL data sets:
Data set
Description
SYSIN
Input data set.
You cannot enter comments in DSNTIAUL input.
The record length for the input data set must be at least 72 bytes. DSNTIAUL reads only the first 72 bytes of each record.
SYSPRINT
Output data set. DSNTIAUL writes informational and error messages in this data set.
The record length for the SYSPRINT data set is 121 bytes.
SYSPUNCH
Output data set. DSNTIAUL writes the LOAD utility control statements in this data set.
SYSRECnn
Output data sets. The value nn ranges from 00 to 99. You can have a maximum of 100 output data sets for a single execution of DSNTIAUL. Each data set contains the data that is unloaded when DSNTIAUL processes a SELECT statement from the input data set. Therefore, the number of output data sets must match the number of SELECT statements (if you specify parameter SQL) or table specifications in your input data set.
Define all data sets as sequential data sets. You can specify the record length and block size of the SYSPUNCH and SYSRECnn data sets. The maximum record length for the SYSPUNCH and SYSRECnn data sets is 32760 bytes.
DSNTIAUL return codes:
Return code Meaning
0 Successful completion.
4 An SQL statement received a warning code. If the SQL statement was a SELECT statement, DB2 did not perform the associated unload operation. If DB2 return a +394, which indicates that it is using optimization hints, DB2 performs the unload operation.
8 An SQL statement received an error code. If the SQL statement was a SELECT statement, DB2 did not perform the associated unload operation.
12 DSNTIAUL could not open a data set, an SQL statement returned a severe error code (-8nn or -9nn), or an error occurred in the SQL message formatting routine.
Examples of DSNTIAUL invocation: Suppose that you want to unload the rows for department D01 from the project table. Because you can fit the table specification on one line, and you do not want to execute any non-SELECT statements, you do not need the SQL parameter. Your invocation looks like the one that is shown in Figure 162:
Figure 162. DSNTIAUL invocation without the SQL parameter
//UNLOAD EXEC PGM=IKJEFT01,DYNAMNBR=20
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DSN)
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIB81) -
LIB('DSN810.RUNLIB.LOAD')
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSREC00 DD DSN=DSN8UNLD.SYSREC00,
// UNIT=SYSDA,SPACE=(32760,(1000,500)),DISP=(,CATLG),
// VOL=SER=SCR03
//SYSPUNCH DD DSN=DSN8UNLD.SYSPUNCH,
// UNIT=SYSDA,SPACE=(800,(15,15)),DISP=(,CATLG),
// VOL=SER=SCR03,RECFM=FB,LRECL=120,BLKSIZE=1200
//SYSIN DD *
DSN8810.PROJ WHERE DEPTNO='D01'
If you want to obtain the LOAD utility control statements for loading rows into a table, but you do not want to unload the rows, you can set the data set names for the SYSRECnn data sets to DUMMY. For example, to obtain the utility control statements for loading rows into the department table, you invoke DSNTIAUL as shown in Figure 163:
Figure 163. DSNTIAUL invocation to obtain LOAD control statements
//UNLOAD EXEC PGM=IKJEFT01,DYNAMNBR=20
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DSN)
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIB81) -
LIB('DSN810.RUNLIB.LOAD')
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSREC00 DD DUMMY
//SYSPUNCH DD DSN=DSN8UNLD.SYSPUNCH,
// UNIT=SYSDA,SPACE=(800,(15,15)),DISP=(,CATLG),
// VOL=SER=SCR03,RECFM=FB,LRECL=120,BLKSIZE=1200
//SYSIN DD *
DSN8810.DEPT
Now suppose that you also want to use DSNTIAUL to do these things:
Unload all rows from the project table
Unload only rows from the employee table for employees in departments with department numbers that begin with D, and order the unloaded rows by employee number
Lock both tables in share mode before you unload them
Retrieve 250 rows per fetch
For these activities, you must specify the SQL parameter and specify the number of rows per fetch when you run DSNTIAUL. Your DSNTIAUL invocation is shown in Figure 164:
Figure 164. DSNTIAUL invocation with the SQL parameter
//UNLOAD EXEC PGM=IKJEFT01,DYNAMNBR=20
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DSN)
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIB81) PARMS('SQL,250') -
LIB('DSN810.RUNLIB.LOAD')
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSREC00 DD DSN=DSN8UNLD.SYSREC00,
// UNIT=SYSDA,SPACE=(32760,(1000,500)),DISP=(,CATLG),
// VOL=SER=SCR03
//SYSREC01 DD DSN=DSN8UNLD.SYSREC01,
// UNIT=SYSDA,SPACE=(32760,(1000,500)),DISP=(,CATLG),
// VOL=SER=SCR03
//SYSPUNCH DD DSN=DSN8UNLD.SYSPUNCH,
// UNIT=SYSDA,SPACE=(800,(15,15)),DISP=(,CATLG),
// VOL=SER=SCR03,RECFM=FB,LRECL=120,BLKSIZE=1200
//SYSIN DD *
LOCK TABLE DSN8810.EMP IN SHARE MODE;
LOCK TABLE DSN8810.PROJ IN SHARE MODE;
SELECT * FROM DSN8810.PROJ;
SELECT * FROM DSN8810.EMP
WHERE WORKDEPT LIKE 'D%'
ORDER BY EMPNO;
DSNTIAUL parameters:
SQL : Specify SQL to indicate that your input data set contains one or more complete SQL statements, each of which ends with a semicolon. You can include any SQL statement that can be executed dynamically in your input data set. In addition, you can include the static SQL statements CONNECT, SET CONNECTION, or RELEASE. DSNTIAUL uses the SELECT statements to determine which tables to unload and dynamically executes all other statements except CONNECT, SET CONNECTION, and RELEASE. DSNTIAUL executes CONNECT, SET CONNECTION, and RELEASE statically to connect to remote locations.
number of rows per fetch
Specify a number from 1 to 32767 to specify the number of rows per fetch that DSNTIAUL retrieves. If you do not specify this number, DSNTIAUL retrieves 100 rows per fetch. This parameter can be specified with the SQL parameter.
#Specify 1 to retrieve data from a remote site when DSNTIAUL #is bound with the DBPROTOCOL(PRIVATE) option.
#TOLWARN
#Specify NO (the default) or YES to indicate whether DSNTIAUL continues #to retrieve rows after receiving an SQL warning: #
#
NO #
If a warning occurs when DSNTIAUL executes an OPEN or FETCH to retrieve #rows, DSNTIAUL stops retrieving rows. If the SQLWARN1, SQLWARN2, SQLWARN6, #or SQLWARN7 flag is set when DSNTIAUL executes a FETCH to retrieve rows, DSNTIAUL #continues to retrieve rows. #
Exception:# #
YES #
If a warning occurs when DSNTIAUL executes an OPEN or FETCH to retrieve #rows, DSNTIAUL continues to retrieve rows. # #
If you do not specify the SQL parameter, your input data set must contain one or more single-line statements (without a semicolon) that use the following syntax: table or view name [WHERE conditions] [ORDER BY columns]
Each input statement must be a valid SQL SELECT statement with the clause SELECT * FROM omitted and with no ending semicolon. DSNTIAUL generates a SELECT statement for each input statement by appending your input line to SELECT * FROM, then uses the result to determine which tables to unload. For this input format, the text for each table specification can be a maximum of 72 bytes and must not span multiple lines.
You can use the input statements to specify SELECT statements that join two or more tables or select specific columns from a table. If you specify columns, you need to modify the LOAD statement that DSNTIAUL generates.
DSNTIAUL data sets:
Data set
Description
SYSIN
Input data set.
You cannot enter comments in DSNTIAUL input.
The record length for the input data set must be at least 72 bytes. DSNTIAUL reads only the first 72 bytes of each record.
SYSPRINT
Output data set. DSNTIAUL writes informational and error messages in this data set.
The record length for the SYSPRINT data set is 121 bytes.
SYSPUNCH
Output data set. DSNTIAUL writes the LOAD utility control statements in this data set.
SYSRECnn
Output data sets. The value nn ranges from 00 to 99. You can have a maximum of 100 output data sets for a single execution of DSNTIAUL. Each data set contains the data that is unloaded when DSNTIAUL processes a SELECT statement from the input data set. Therefore, the number of output data sets must match the number of SELECT statements (if you specify parameter SQL) or table specifications in your input data set.
Define all data sets as sequential data sets. You can specify the record length and block size of the SYSPUNCH and SYSRECnn data sets. The maximum record length for the SYSPUNCH and SYSRECnn data sets is 32760 bytes.
DSNTIAUL return codes:
Return code Meaning
0 Successful completion.
4 An SQL statement received a warning code. If the SQL statement was a SELECT statement, DB2 did not perform the associated unload operation. If DB2 return a +394, which indicates that it is using optimization hints, DB2 performs the unload operation.
8 An SQL statement received an error code. If the SQL statement was a SELECT statement, DB2 did not perform the associated unload operation.
12 DSNTIAUL could not open a data set, an SQL statement returned a severe error code (-8nn or -9nn), or an error occurred in the SQL message formatting routine.
Examples of DSNTIAUL invocation: Suppose that you want to unload the rows for department D01 from the project table. Because you can fit the table specification on one line, and you do not want to execute any non-SELECT statements, you do not need the SQL parameter. Your invocation looks like the one that is shown in Figure 162:
Figure 162. DSNTIAUL invocation without the SQL parameter
//UNLOAD EXEC PGM=IKJEFT01,DYNAMNBR=20
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DSN)
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIB81) -
LIB('DSN810.RUNLIB.LOAD')
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSREC00 DD DSN=DSN8UNLD.SYSREC00,
// UNIT=SYSDA,SPACE=(32760,(1000,500)),DISP=(,CATLG),
// VOL=SER=SCR03
//SYSPUNCH DD DSN=DSN8UNLD.SYSPUNCH,
// UNIT=SYSDA,SPACE=(800,(15,15)),DISP=(,CATLG),
// VOL=SER=SCR03,RECFM=FB,LRECL=120,BLKSIZE=1200
//SYSIN DD *
DSN8810.PROJ WHERE DEPTNO='D01'
If you want to obtain the LOAD utility control statements for loading rows into a table, but you do not want to unload the rows, you can set the data set names for the SYSRECnn data sets to DUMMY. For example, to obtain the utility control statements for loading rows into the department table, you invoke DSNTIAUL as shown in Figure 163:
Figure 163. DSNTIAUL invocation to obtain LOAD control statements
//UNLOAD EXEC PGM=IKJEFT01,DYNAMNBR=20
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DSN)
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIB81) -
LIB('DSN810.RUNLIB.LOAD')
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSREC00 DD DUMMY
//SYSPUNCH DD DSN=DSN8UNLD.SYSPUNCH,
// UNIT=SYSDA,SPACE=(800,(15,15)),DISP=(,CATLG),
// VOL=SER=SCR03,RECFM=FB,LRECL=120,BLKSIZE=1200
//SYSIN DD *
DSN8810.DEPT
Now suppose that you also want to use DSNTIAUL to do these things:
Unload all rows from the project table
Unload only rows from the employee table for employees in departments with department numbers that begin with D, and order the unloaded rows by employee number
Lock both tables in share mode before you unload them
Retrieve 250 rows per fetch
For these activities, you must specify the SQL parameter and specify the number of rows per fetch when you run DSNTIAUL. Your DSNTIAUL invocation is shown in Figure 164:
Figure 164. DSNTIAUL invocation with the SQL parameter
//UNLOAD EXEC PGM=IKJEFT01,DYNAMNBR=20
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DSN)
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIB81) PARMS('SQL,250') -
LIB('DSN810.RUNLIB.LOAD')
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSREC00 DD DSN=DSN8UNLD.SYSREC00,
// UNIT=SYSDA,SPACE=(32760,(1000,500)),DISP=(,CATLG),
// VOL=SER=SCR03
//SYSREC01 DD DSN=DSN8UNLD.SYSREC01,
// UNIT=SYSDA,SPACE=(32760,(1000,500)),DISP=(,CATLG),
// VOL=SER=SCR03
//SYSPUNCH DD DSN=DSN8UNLD.SYSPUNCH,
// UNIT=SYSDA,SPACE=(800,(15,15)),DISP=(,CATLG),
// VOL=SER=SCR03,RECFM=FB,LRECL=120,BLKSIZE=1200
//SYSIN DD *
LOCK TABLE DSN8810.EMP IN SHARE MODE;
LOCK TABLE DSN8810.PROJ IN SHARE MODE;
SELECT * FROM DSN8810.PROJ;
SELECT * FROM DSN8810.EMP
WHERE WORKDEPT LIKE 'D%'
ORDER BY EMPNO;
DSNTEP2
A sample dynamic SQL program that is written in the PL/I language. With this program, you can execute any SQL statement that can be executed dynamically. You can use the source version of DSNTEP2 and modify it to meet your needs, or, if you do not have a PL/I compiler at your installation, you can use the object code version of DSNTEP2
DSNTEP2 is an application program that can be used to issue DB2 dynamic SQL statements. It is sometimes referred to as “Batch SPUFI” because it allows you to submit SQL in batch similar to how SPUFI allows online SQL execution.The following sample JCL demonstrates the capability of DSNTEP2 to issue DCL, DDL, and DML dynamically.
//PH063S04 EXEC PGM=IKJEFT01
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DB8A)
RUN PROGRAM(DSNTEP2) PLAN(DSNTEP81) -
LIBRARY('DB8A.DSN810.RUNLIB.LOAD')
END
//SYSIN DD *
Select current timestamp from sysibm.sysdummy1;
/*
DSNTEP2 is an application program that can be used to issue DB2 dynamic SQL statements. It is sometimes referred to as “Batch SPUFI” because it allows you to submit SQL in batch similar to how SPUFI allows online SQL execution.The following sample JCL demonstrates the capability of DSNTEP2 to issue DCL, DDL, and DML dynamically.
//PH063S04 EXEC PGM=IKJEFT01
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DB8A)
RUN PROGRAM(DSNTEP2) PLAN(DSNTEP81) -
LIBRARY('DB8A.DSN810.RUNLIB.LOAD')
END
//SYSIN DD *
Select current timestamp from sysibm.sysdummy1;
/*
DB2 OPTHINT
This option is used in DB2 to influence the DB2 to use some of the old tested access paths rather than the ones created as a result of rebind.
Subscribe to:
Posts (Atom)