很多工具，我们每天都在使用，但是我们真的认真的思考过没？当你真正的遇到问题的时候，你就束手无策了
DB2 DBA人员日常工作中经常使用的一个工具，可以它的功能你真的清楚么，我们在这里简单的介绍一下：
DSNTIAUL1 is a IBM DB2 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.
DSNTIAUL unloads the rows in a sequential file without row or column delimiters, which is compatible
with the LOAD utility. It also generates utility control statements for the LOAD. DSNTIAUL also lets
you execute any SQL non-SELECT statement that can be executed dynamically.
类似的程序：
Other useful sample programs are ...
?
DSNTIAD: A sample dynamic SQL program that is written in assembler language. With this
program, you can execute any SQL statement that can be executed dynamically, except a
SELECT statement.
?
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.
? DSNTEP4: 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
install infor:
Default load library: DSNvv0.RUNLIB.LOAD(DSNTIAUL)
Default plan name: PLAN(DSNTIBvv) MEM(DSNTIAUL)
参数的含义：
Arguments to DSNTIAUL using PARM(‘parm,parm, ...’):
? 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.
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.
? YES: If a warning occurs when DSNTIAUL executes an OPEN or FETCH to retrieve rows,
DSNTIAUL continues to retrieve rows.
性能影响：
Performance
The default value for DSNTIAUL retrieves 100 rows per fetch.
When you retrieve 1000 or 10000 rows,
the CPU time stays almost the same. In most cases the value of 32767 will show the best
performance. However, consumption depends on number of columns and type of selection.
经验分享：很是有意思，有兴趣的筒子可以好好的读读
I usually blog over the weekend, you might be wondering why am I getting a new blog post alert from Vikram. Yesterday I got a problem and I used DSNTIAUL program to get rid of the problem. Thought of blogging it here before it evaporates out of my mind.
Now getting into the details of the problem, we had recently upgraded the CA DB2 tools on our shop from R11.5 SP0 to R11.5 SP2. In one of the upgrade steps we have to run a job which does the create of new DB2 objects some LOADs, UNLOADs etc. This job failed while running some utility on the Tablespace PTITSPAG, here I created some mess in fixing the problem which I couldn't remember exactly and this has resulted in deleting the underlying physical VSAM dataset for PTITSPAG. I haven't realized this at that time.
After a few days later when one of the image copy jobs failed while taking image copy of this Tablespace with this error
DSNUGUTC - COPY TABLESPACE PTDB.PTITSPAG COPYDDN SYSCOPY FULL YES
DSNUGBAC - RESOURCE UNAVAILABLE
REASON 00D70024
TYPE 00000220
NAME WAB242A2.DSNDBC.PTDB.PTITSPAG.J0001.A001
DSNUGBAC - UTILITY DATA BASE SERVICES MEMORY EXECUTION ABENDED
This Tablespace has one table and the data in it is very much static i.e haven't had any changes since the upgrade has happened (This we have compared from the data in the table from other DB2 region). We are able to get the data in the table (You might be wondering how is it possible without the physical VSAM dataset for the Tablespace existing, it is very much possible. I will explain it at the end of this blog).
We thought of doing a recovery by Unloading this data into a flat file. Save the Table DDL, Tablespace DDL. Drop the Tablespace, Recreate it using the saved DDL, recreate the table and then load it back.
To my innocence I started the UNLOAD utility and got the same resource unavailable message. Then DSNTIAUL program came to my rescue.
Now some of you will think, that the only tip about DSNTIAUL is: Don't use this old, slow and obsolete product. But maybe you have overlooked a little detail. DSNTIAUL is able to execute any SQL SELECT statement of your liking. The only limitation is the length and the general limits of SQL SELECT statements in DB2. You can unload any DB2-data in any form you like with DSNTIAUL. It might not be fast, but you can use JOINS and UNIONS and all the other facilities provided by SQL. Only your imagination sets the limits. DSNTIAUL is documented in the
The JCL I used is like this
//S030 EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT),TIME=60
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DBxx)
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) PARMS('SQL') -
LIB('dsn810.DBxx.RUNLIB.LOAD')
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=U
//SYSREC00 DD DSN=TS.PTITSPAG.RECORD,
// DISP=(MOD,CATLG),
// SPACE=(16384,(20,20),,,ROUND),
// UNIT=SYSDA
//SYSPUNCH DD DSN=TS.PTITSPAG.PUNCHD,
// DISP=(MOD,CATLG),
// SPACE=(16384,(20,20),,,ROUND),
// UNIT=SYSDA
//SYSIN DD *
SELECT RULE_NUMBER,DB2V,REC_NUMBER FROM PTI.PTPA_ES_RU2RE_0107;
Then I did the remaining tasks as I have mentioned and hence happy ending. Now coming to the question how it worked without the physical VSAM dataset ? Even I had this doubt but I ran an explain on the select statement and my PLAN_TABLE showed these values
ACCESSTYPE MATCHCOLS ACCESSCREATOR ACCESSNAME INDEXONLY
---+---------+---------+---------+---------+---------+---------+----
I 0 PTI PTPA_IX_RU2RE_0107 Y
It worked because the optimizer chose INDEXONLY access. Hence it didn't refer the TABLESPACE VSAM dataset. The UNLOAD failed becuase it had to go through the TABLESPACE VSAM dataset.
summary:
Another important difference is that UNLOAD always does a tablespace scan,
while DSNTIAUL executes a dynamic SQL statement and thus the optimiser may
be able to choose a better access path.
ref:
http://it.toolbox.com/blogs/db2-dba-diary/dsntiaul-how-good-is-it-compared-to-unload-utility-35031
