II13592: USING DSNTIAUL TO EXTRACT DB2 DATA FROM NON-OS/390 AND Z/OS PLATFORMS ( SQLCODE104 UNLOAD DISTRIBUTED )

APAR status

• Closed as canceled.

Error description

• This document describes how you can use the DB2 sample unload
  tool DSNTIAUL to extract data from DB2s not on z/OS.
  .
  DSNTIAUL is officially supported for use on DB2 for z/OS only.
  The restriction exists because DSNTIAUL is dependent
  on the DB2 for z/OS parser to locate the terminating
  semicolon of each SQL statement in the SYSIN DD input stream.
  When the DSNTIAUL connection changes to DB2 on an alternate
  platform, parsers there flag the terminating semicolon as an
  error (SQLCODE -104) and reject the SQL statement.
  .
  Nevertheless, there is an unofficial workaround that allows you
  to use DSNTIAUL to unload data from DB2s running on alternate
  platforms. Though it lacks the capability to process SQL, it is
  effective for simple unloads. Requirements vary according to
  whether you are using the DSNTIAUL provided in a release prior
  to DB2 UDB for z/OS Version 8:
  .
  (1) Your environment must be configured to connect from DB2 for
      z/OS to a remote server on an alternate platform.  This
      document presumes that you can already do so.
  .
  (2) To bind a package for the DSNTIAUL provided with DB2 V8 or a
      subsequent release on a non-z/OS DB2, you need to add the
      SQLERROR(CONTINUE) option.
  .
  (3) You need to use 3-part names to reference tables on the
      remote server. This permits DB2 to access data on the remote
      server with an implicit connection.
  .
  (4) You need to run DSNTIAUL in so-called "non-SQL" mode.
      DSNTIAUL has two modes: SQL and non-SQL. SQL mode permits
      DSNTIAUL to interpret and process full SQL requests but, as
      indicated above, requires a connection to DB2 for z/OS.
      Non-SQL mode accepts a 1-, 2-, or 3-part name for a
      table to be unloaded and allows a WHERE clause for filter-
      ing. However:
      - When using the DSNTIAUL provided with DB2 V7 and earlier
        releases, the entire request must fit on a single 72-byte
        input record.
      - When using the DSNTIAUL provided with DB2 V8 or a subse-
        quent release, the table name can wrap onto subsequent
        records but additional terms must fit onto the same
        record that the table name ends on.
  
      Regardless of the DSNTIAUL version, you cannot request table
      joins or specific column names in non-SQL mode.
  .
  (5) To run the DSNTIAUL provided with DB2 V8 or a subsequent
      release, you also need to disable multi-row FETCH mode.
      Beginning in DB2 V8, DSNTIAUL has two cursors: The tradi-
      tional one for single row FETCH and a new one for multi-row
      FETCH, which offers enhanced performance but which is not
      available on all DB2 platforms.  Beginning with V8, in order
      to use DSNTIAUL on a platform that does not support multi-
      row FETCH compatible with DB2 for z/OS, you pass a rowfetch
      size of 1 via to DSN PARMS parameter, for example:
  .
      RUN  PROGRAM(DSNTIAUL) PARMS('1')
  .
  Suppose you want to use DSNTIAUL to extract data from a DB2
  server on Windows called MYDB2WIN (this procedure applies to
  other platforms as well as Windows). Bind the package and plan
  on DB2 for z/OS as follows:
  
  
  DSN SYSTEM(V81A)
  
  BIND PACKAGE(MYDB2WIN.DSNTIB81) MEM(DSNTIAUL) ACTION(REPLACE) -
       DBPROTOCOL(DRDA) ISOLATION(CS) VALIDATE(BIND) -
       SQLERROR(CONTINUE)
  
  BIND PACKAGE(DSNTIB81) MEM(DSNTIAUL) ACTION(REPLACE) -
       DBPROTOCOL(DRDA) ISOLATION(CS) VALIDATE(BIND)
  
  BIND PLAN(DSNTIB81) PKLIST(*.DSNTIB81.DSNTIAUL) -
       ACTION(REPLACE) ISOLATION(CS) VALIDATE(BIND)
  
  ** The SQLERROR(CONTINUE) clause on the remote BIND PACKAGE
     statement is required only when binding the DSNTIAUL provided
     with DB2 V8 or a subsequent release.
  .
  You can then use three part names to unload from a remote system
  and using the non-SQL mode of DSNTIAUL. Here's the difference
  between SQL mode and non-SQL mode:
  SQL mode (works only when connected to a DB2 for z/OS server):
  
  //UNLOAD EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT)
  //SYSTSPRT DD SYSOUT=*
  //SYSTSIN DD *
  DSN SYSTEM(V81A)
  RUN PROGRAM(DSNTIAUL) PLAN(DSNTIB81) PARMS('SQL') -
  LIB('USER.RUNLIB.LOAD')
  //SYSPRINT DD SYSOUT=*
  //SYSUDUMP DD SYSOUT=*
  //SYSREC00 DD DSN=USRT003.G.DSN8UNLD.SYSREC00,
  //SYSREC..
  //SYSIN DD *
  LOCK TABLE DSN8810.DEPT IN SHARE MODE;
  SELECT * FROM DSN8810.DEPT WHERE DEPTNO = 'ABC';
  SELECT * FROM DSN8810.VPHONE;
  //*
  .
  Non-SQL mode (can be used on other DB2 platforms):
  //UNLOAD EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT)
  //SYSTSPRT DD SYSOUT=*
  //SYSTSIN DD *
  DSN SYSTEM(V81A)
  RUN PROGRAM(DSNTIAUL) PLAN(DSNTIB81) PARMS('1') -
  LIB('USER.RUNLIB.LOAD')
  //SYSPRINT DD SYSOUT=*
  //SYSUDUMP DD SYSOUT=*
  //SYSREC00 DD DSN=USRT003.G.DSN8UNLD.SYSREC00,
  //SYSREC..
  //SYSIN DD *
  MYDB2WIN.SAMPLE.DEPT WHERE DEPTNO = 'ABC'
  MYDB2WIN.SAMPLE.VPHONE
  //*
  .
  Note that:
  * The PARMS setting specifies '1'
    -> Do not use PARMS('1') with the DSNTIAUL provided with DB2
       V7 and earlier releases
  * The SELECT syntax is removed
  * There are no terminating semicolons
  * You cannot request particular columns, do joins, etc.
  * You can specify a WHERE clause but:
    - For DSNTIAUL prior to V8, the entire "statement" has to fit
      on a single input record, between columns 1-72
    - For V8 DSNTIAUL and subsequent releases, the 3-part table
      name can wrap onto additional lines but additional terms
      must fit onto the same record that the table name ends on.
  * You can't process the LOCK statement
  .
  Refer to the section entitled "Running DSNTIAUL" in Appendix D
  (Running DSNTIAUL, DSNTIAD, and DSNTEP2) of the DB2 for z/OS
  Utilities Guide and Reference for information about how to use
  DSNTIAUL without SQL mode.
  ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
  additional search arguments 5740xyr00 r810 r910 sqlcode804
  

Local fix

Problem summary

Problem conclusion

Temporary fix

Comments

• Informational APAR for using DSNTIAUL to extract data
  from DB2's not running on OS/390 or z/OS
  

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

Applicable component levels