Replication Guide and Reference

Capture and Apply for OS/390

This chapter describes how to set up and operate the Capture and Apply programs for OS/390. It also contains information specific to replicating DB2 for OS/390 data:

Rules for index types
Using the DB2 ODBC Catalog

Setting up the Capture and Apply programs

Setting up consists of applying DB2 maintenance; installing the Capture and Apply programs and configuring the source, target, and control servers; and configuring Capture and Apply after you install a new release of DB2.

Applying DB2 maintenance

Make sure to apply the correct DB2 maintenance before installing the Capture and Apply for OS/390 programs. To apply the correct DB2 maintenance:

Install the maintenance listed in the Service Level Information section of the DB2 UDB for OS/390 V7 Program Directory and review the Preventive Service Planning (PSP) buckets before installing Capture or Apply.
Install the maintenance listed in the PSP buckets for Capture and for Apply. Refer to the Preventive Service Planning section of the Program Directory.

Installing Capture and Apply for OS/390

Capture for OS/390 and Apply for OS/390 are packaged in SMP/E format. The installation sequence for each program consists of:

Customizing invocation JCL to suit your environment
Using SMP/E to install
Providing APF authorization
Creating and loading the VSAM messages file
Binding to the DB2 subsystem and target or control subsystems that the DB2 subsystem will connect to

See the DB2 Universal Database for OS/390 Version 7 Program Directory for complete installation instructions for the Capture and Apply programs.

Configuring Capture and Apply for OS/390 after installing a new release of DB2

Capture and Apply load modules for OS/390 are automatically link-edited by SMP/E using the DB2 DSN###.SDSNLOAD library specified for your DB2 release. The sample jobs ASNLDEF (Capture) and ASNADEF (Apply) are used during install to define data sets to your SMP/E system. There is one load module per program for each release of DB2, with the suffix representing the DB2 release:

DB2 release Capture load module Apply load module
Version 5.1 ASNLRP75 ASNAPV75
Version 6.1 ASNLRP76 ASNAPV76
Version 7.1 ASNLRP77 ASNAPV77

DB2 release	Capture load module	Apply load module
Version 5.1	ASNLRP75	ASNAPV75
Version 6.1	ASNLRP76	ASNAPV76
Version 7.1	ASNLRP77	ASNAPV77

To set up your SMP/E DDDEF entries during installation (in 2), refer to the Capture sample library SASNLBSE(ASNLDEF) and the Apply sample library SASNABSE(ASNADEF).

When you installed the Capture or Apply program, each DDDEF entry was set up to point to a corresponding DB2 DSN###.SDSNLOAD library, where ### referred to the product release (710 for Version 7.1, 610 for Version 6.1, and 510 for Version 5.1). If any of the related releases of DB2 were not installed, then the DDDEF entries were set up with the DB2 SDSNLOAD library for "DDDEF(SDSNLD##)" to point to the highest installed level of DB2. For example, if DB2 5.1 was not installed, then DDDEF entries were set up with DDDEF(SDSNLD51) to point to the DSN710.SDSNLOAD library so that SMP/E link-edit will complete with return code 4. Also, the Capture load module ASNLRP75 in run job ASNL2RN5 or the Apply load module ASNAPV75 in run job ASNA2RN5 cannot be executed.

If you install a new release of DB2 after installing the Capture or Apply program:

Use the Capture sample DDDEF job SASNLBSE(ASNLDEF), Apply sample DDDEF job SASNABSE(ASNADEF), or both, to change the DB2 DSN###.SDSNLOAD library data set for DDDEF(SDSNLD##) to the new installed level of DB2. Note that SDSNLD## refers to the new DB2 release (51, 61, 71 ) and DSN###.SDSNLOAD refers to the new DB2 release (510, 610, or 710).
Run an SMP/E APPLY job to reapply any recent Capture V7 PTF, any recent Apply V7 PTF, or both, using the SMP/E REDO operand.

Operating Capture for OS/390

An administrator can use the commands in this section to perform the following Capture for OS/390 tasks:

Starting the Capture program
Scheduling the Capture program
Stopping the Capture program
Suspending the Capture program
Resuming the Capture program
Reinitializing the Capture program
Pruning the change data and unit-of-work tables
Displaying captured log progress

You can submit the commands from TSO or the MVS console.

This section also lists restrictions for running the Capture program.

Restrictions for running the Capture program

Capture for OS/390 cannot replicate certain types of data. See General restrictions for replication for a list of restrictions.

Only one instance of the Capture program can run on a subsystem. In a data-sharing environment, only one of any member subsystems in a data-sharing group can run the Capture program. In a data-sharing environment, DB2 presents merged log records from all member subsystems.

Starting Capture for OS/390

After you start the Capture program, it runs continuously until you stop it or it detects an unrecoverable error. You can start it either with JCL or as a system started task.

To start the Capture for OS/390 program with JCL:

Prepare the JCL for OS/390 by specifying the appropriate optional invocation parameters in the PARM field of the Capture job. Customize the JCL in library SASNLJCL(ASNL2RN#) to meet your site's requirements.
An example of this line in the invocation JCL is:
```
//ASNL2RNx EXEC PGM=ASNLRPnn,PARM='DB2_subsystem_name NOTERM WARMNS SLEEP=2'
```
where x and nn indicate the level of the Capture program, as follows:
- For the Capture program running on DB2 for OS/390 Version 5 Release 1, x is 5 and nn is 75.
- For the Capture program running on DB2 for OS/390 Version 6, x is 6 and nn is 76.
- For the Capture program running on DB2 for OS/390 Version 7, x is 7 and nn is 77.
Submit the JCL from TSO or from the MVS console. Capture for OS/390 can run either as a batch job or a started task.

To start the Capture for OS/390 program as a system-started task:

Create a procedure (procname) in your PROCLIB. This procedure contains the JCL (for example ASNL2RN5, ASNL2RN6, or ASNL2RN7 in library SASNLJCL) required to run the Capture program.
Update module ICHRIN03 (RACF), which associates procname with the user ID to be used to start the Capture program. Make sure that the necessary DB2 authorization is granted to this user ID before you start the Capture program.
Build this module in SYS1.LPALIB. You must re-IPL the MVS system to activate these changes.
From the MVS system console, run the command start procname.

Table 24 defines the invocation parameters.

Table 24. Capture invocation parameter definitions for OS/390

Parameter Definition
DB2_subsystem_name Specifies the name of the DB2 subsystem that can connect to the control server. The default for the subsystem name is DSN. This parameter must be the first parameter.
For data sharing, do not use the group attach name. Instead, specify a member subsystem name.
TERM (default) Terminates the Capture program if DB2 is terminated.
NOTERM Keeps the Capture program running if DB2 is terminated with MODE(QUIESCE). When DB2 initializes, the Capture program starts in WARM mode and begins capturing where it left off when DB2 terminated.
If DB2 terminates via FORCE or due to abnormal termination, the Capture program terminates even if you selected this parameter.
If you use the NOTERM option and start DB2 with restricted access (ACCESS MAINT), the Capture program will not be able to connect and will terminate.
WARM (default) The Capture program resumes processing where it ended in its previous run if warm start information is available. If the Capture program cannot warm start, it switches to a cold start.
WARMNS The Capture program resumes processing where it ended in its previous run if warm start information is available. Otherwise, it issues a message and terminates. With WARMNS, the Capture program does not automatically switch to a cold start. The Capture program leaves the trace, UOW, CD, and warm start tables intact. In case of errors, the Capture program terminates instead of switching to a cold start as when WARM is specified.
COLD The Capture program starts up by deleting all rows in its CD table, UOW table, and trace table during initialization. All subscriptions to these replication sources will be fully refreshed during the next Apply processing cycle. A full refresh is not done if the target is a noncomplete CCD table (see Resolving gaps between source and target tables).
PRUNE (default) The Capture program automatically prunes the rows in the CD and UOW tables that the Apply program copied at the interval specified in the tuning parameters table. The Capture program also prunes the CD and UOW rows that are older than the retention limit, even though they have not been copied during warm start.
NOPRUNE Automatic pruning is disabled. The Capture program prunes the CD and UOW tables only when you enter the PRUNE command.
NOTRACE (default) No trace information is written.
TRACE Writes trace messages to the standard output, SYSPRINT.
SLEEP=n Specifies the number of seconds that the Capture program will wait when it finishes processing the active log, where n is the number of seconds. This parameter is available for the Capture program running on DB2 for MVS Version 4 Release 1 and later with data sharing. The default is SLEEP=0.
ALLCHG (default) Specifies that an entry is made to the CD table whenever any source table row changes.
CHGONLY Specifies that an entry is made to the CD table when a source table row changes only if the columns defined for replication (CD table columns) change values.

Parameter	Definition
`DB2_subsystem_name`	Specifies the name of the DB2 subsystem that can connect to the control server. The default for the subsystem name is DSN. This parameter must be the first parameter. For data sharing, do not use the group attach name. Instead, specify a member subsystem name.
TERM (default)	Terminates the Capture program if DB2 is terminated.
NOTERM	Keeps the Capture program running if DB2 is terminated with MODE(QUIESCE). When DB2 initializes, the Capture program starts in WARM mode and begins capturing where it left off when DB2 terminated. If DB2 terminates via FORCE or due to abnormal termination, the Capture program terminates even if you selected this parameter. If you use the NOTERM option and start DB2 with restricted access (ACCESS MAINT), the Capture program will not be able to connect and will terminate.
WARM (default)	The Capture program resumes processing where it ended in its previous run if warm start information is available. If the Capture program cannot warm start, it switches to a cold start.
WARMNS	The Capture program resumes processing where it ended in its previous run if warm start information is available. Otherwise, it issues a message and terminates. With WARMNS, the Capture program does not automatically switch to a cold start. The Capture program leaves the trace, UOW, CD, and warm start tables intact. In case of errors, the Capture program terminates instead of switching to a cold start as when WARM is specified.
COLD	The Capture program starts up by deleting all rows in its CD table, UOW table, and trace table during initialization. All subscriptions to these replication sources will be fully refreshed during the next Apply processing cycle. A full refresh is not done if the target is a noncomplete CCD table (see Resolving gaps between source and target tables).
PRUNE (default)	The Capture program automatically prunes the rows in the CD and UOW tables that the Apply program copied at the interval specified in the tuning parameters table. The Capture program also prunes the CD and UOW rows that are older than the retention limit, even though they have not been copied during warm start.
NOPRUNE	Automatic pruning is disabled. The Capture program prunes the CD and UOW tables only when you enter the PRUNE command.
NOTRACE (default)	No trace information is written.
TRACE	Writes trace messages to the standard output, SYSPRINT.
SLEEP=`n`	Specifies the number of seconds that the Capture program will wait when it finishes processing the active log, where `n` is the number of seconds. This parameter is available for the Capture program running on DB2 for MVS Version 4 Release 1 and later with data sharing. The default is SLEEP=0.
ALLCHG (default)	Specifies that an entry is made to the CD table whenever any source table row changes.
CHGONLY	Specifies that an entry is made to the CD table when a source table row changes only if the columns defined for replication (CD table columns) change values.

Scheduling Capture for OS/390

Use either the $TA JES2 command or the AT NetView command to start Capture for OS/390 at a specific time. You must:

Create a procedure that calls Capture for OS/390 in the PROCLIB.
Modify the ICHRIN03 RACF module (or appropriate definitions for your MVS security package) to associate the procedure with a user ID.
Link-edit the module in SYS1.LPALIB.

Stopping Capture for OS/390

Use the STOP command to stop the Capture program gracefully and commit the log records that it processed up to that point. (See MVS System Commands for details.)

>>-F--jobname--,STOP-------------------------------------------><

Issue the STOP command before:

Removing an existing replication source
Opening and modifying an existing replication source
Shutting down the database

Suspending Capture for OS/390

Use the SUSPEND command to relinquish OS/390 resources to operational transactions during peak periods without destroying the Capture program environment. This command suspends the Capture program until you issue the RESUME command.

>>-F--jobname--,SUSPEND----------------------------------------><

Important: Do not use SUSPEND when canceling a replication source. Instead, stop the Capture program by entering the STOP command.

Resuming Capture for OS/390

Use the RESUME command to resume the suspended Capture program.

>>-F--jobname--,RESUME-----------------------------------------><

Reinitializing Capture for OS/390

Use the REINIT command to begin to capture changes from new source tables if you add a new replication source. The REINIT command tells the Capture program to obtain newly added replication sources from the register table.

REINIT also rereads the tuning parameters table for any changes made to the tuning parameters.

>>-F--jobname--,REINIT-----------------------------------------><

Important: Do not use the REINIT command to reinitialize the Capture program after canceling a replication source or dropping a replication source table while the Capture program is running. Instead, stop the Capture program and start it again using the WARM or WARMNS option.

Before you add a column to a replication source or CD table using the ALTER TABLE statement, you must ensure that the Capture program has captured all the changes for the table. After the ALTER TABLE statement, you must issue the REINIT command.

Pruning the change data and unit-of-work tables.

Use the PRUNE command to start pruning the CD and UOW tables.

This command prunes tables once.

>>-F--jobname--,PRUNE------------------------------------------><

The Capture program issues the message ASN0124I when the command is successfully queued.

If you stop or suspend the Capture program during pruning, you must enter the PRUNE command again to resume pruning. Pruning does not resume after you enter the RESUME command.

Displaying captured log progress.

Use the GETLSEQ command to get the timestamp and current log sequence number. You can use this information to determine how far the Capture program has read the DB2 log.

>>-F--jobname--,GETLSEQ----------------------------------------><

The Capture program issues the message ASN0125I indicating when the current log sequence number is successfully processed.

Operating Apply for OS/390

An administrator can use the commands in the following sections to perform the following Apply for OS/390 tasks:

Starting the Apply program
Scheduling the Apply program
Stopping the Apply program

You can submit the commands from TSO or an MVS console.

Starting Apply for OS/390

After you start the Apply program, it runs continuously until:

You stop it in an orderly way.
You cancel it.
An unexpected error or failure occurs.

To start the Apply for OS/390 program:

Prepare the JCL for OS/390 by specifying the appropriate invocation parameters in the PARM field of the Apply job. Customize the JCL to meet your site's requirements. Invocation JCL in library SASNAJCL(ASNA2RN#) is included with the Apply for OS/390 product.

An example of this line in the invocation JCL is:

//ASNARUN EXEC PGM=ASNAPVnn,PARM='Apply_qual DB2_subsystem_name DISK'

Where nn is the level the Apply program, as follows:

For the Apply program running on DB2 for OS/390 Version 5 Release 1, nn is 75.
For the Apply program running on DB2 for OS/390 Version 6, nn is 76.
For the Apply program running on DB2 for OS/390 Version 7, nn is 77.

Table 25 defines the invocation parameters.

Table 25. Apply invocation parameter definitions

Parameter Definition
Apply_qual Specifies the Apply qualifier that the Apply instance uses to identify the subscription sets to be served. The Apply qualifier is case sensitive and must match the value of APPLY_QUAL in the subscription set table. This must be the first parameter.
DB2_subsystem_name Specifies the name of the DB2 subsystem that can connect to the control server. This parameter must be the second parameter.
For data sharing, do not use the group attach name. Instead, specify a member subsystem name.
Control_server_name Specifies the name of the server where the replication control tables will reside. If you do not specify this parameter, the default is the current server.
LOADXit Specifies that the Apply program is to invoke ASNLOAD, an IBM-supplied exit routine that uses the export and load utilities to refresh target tables. Currently, no utility program is available for use by ASNLOAD on DB2 for OS/390.
NOLOADXit (default) Specifies that the Apply program will not invoke ASNLOAD.
MEMory (default) Specifies that a memory file stores the fetched answer set. The Apply program fails if there is insufficient memory for the answer set.
DISK Specifies that a disk file stores the fetched answer set.
INAMsg (default) Specifies that the Apply program is to issue a message when the Apply program is inactive.
NOINAMsg Specifies that the Apply program will not issue this message.
NOTRC (default) Specifies that the Apply program does not generate a trace.
TRCERR Specifies that the Apply program generates a trace that contains only error information.
TRCFLOW Specifies that the Apply program generates a trace that contains error and execution flow information.
NOTIFY Specifies that the Apply program is to invoke ASNDONE, an exit routine that returns control to the user when the Apply program finishes copying a subscription set.
NONOTIFY (default) Specifies that the Apply program will not invoke ASNDONE.
SLEEP (default) Specifies that the Apply program is to go to sleep if no new subscriptions are eligible for processing.
NOSLEEP Specifies that the Apply program is to stop if no new subscription sets are eligible for processing.
DELAY(n) Specifies the delay time (in seconds) at the end of each Apply cycle when continuous replication is used, where n=0, 1, 2, 3, 4, 5, or 6. The default delay time is 6 seconds.
ERRWAIT(n) Specifies the number of seconds that the Apply program waits before retrying after it encounters an error condition, where n is the number of seconds. Do not specify too small a number because the Apply program will run almost continuously and generate many rows in the Apply trail table. The default wait period is 300 seconds (5 minutes).

Parameter	Definition
`Apply_qual`	Specifies the Apply qualifier that the Apply instance uses to identify the subscription sets to be served. The Apply qualifier is case sensitive and must match the value of APPLY_QUAL in the subscription set table. This must be the first parameter.
`DB2_subsystem_name`	Specifies the name of the DB2 subsystem that can connect to the control server. This parameter must be the second parameter. For data sharing, do not use the group attach name. Instead, specify a member subsystem name.
`Control_server_name`	Specifies the name of the server where the replication control tables will reside. If you do not specify this parameter, the default is the current server.
LOADXit	Specifies that the Apply program is to invoke ASNLOAD, an IBM-supplied exit routine that uses the export and load utilities to refresh target tables. Currently, no utility program is available for use by ASNLOAD on DB2 for OS/390.
NOLOADXit (default)	Specifies that the Apply program will not invoke ASNLOAD.
MEMory (default)	Specifies that a memory file stores the fetched answer set. The Apply program fails if there is insufficient memory for the answer set.
DISK	Specifies that a disk file stores the fetched answer set.
INAMsg (default)	Specifies that the Apply program is to issue a message when the Apply program is inactive.
NOINAMsg	Specifies that the Apply program will not issue this message.
NOTRC (default)	Specifies that the Apply program does not generate a trace.
TRCERR	Specifies that the Apply program generates a trace that contains only error information.
TRCFLOW	Specifies that the Apply program generates a trace that contains error and execution flow information.
NOTIFY	Specifies that the Apply program is to invoke ASNDONE, an exit routine that returns control to the user when the Apply program finishes copying a subscription set.
NONOTIFY (default)	Specifies that the Apply program will not invoke ASNDONE.
SLEEP (default)	Specifies that the Apply program is to go to sleep if no new subscriptions are eligible for processing.
NOSLEEP	Specifies that the Apply program is to stop if no new subscription sets are eligible for processing.
DELAY(`n`)	Specifies the delay time (in seconds) at the end of each Apply cycle when continuous replication is used, where `n`=0, 1, 2, 3, 4, 5, or 6. The default delay time is 6 seconds.
ERRWAIT(`n`)	Specifies the number of seconds that the Apply program waits before retrying after it encounters an error condition, where `n` is the number of seconds. Do not specify too small a number because the Apply program will run almost continuously and generate many rows in the Apply trail table. The default wait period is 300 seconds (5 minutes).

Scheduling Apply for OS/390

Use either the $TA JES2 command or the AT NetView command to start Apply for OS/390 at a specific time. You must:

Create a procedure that calls Apply for OS/390 in the PROCLIB.
Modify the ICHRIN03 RACF module (or appropriate definitions for your MVS security package) to associate the procedure with a user ID.
Link-edit the module in SYS1.LPALIB.

See MVS/ESA JES2 Commands for more information about using the $TA JES2 command, and the NetView for MVS Command Reference for more information about using the AT NetView command.

Stopping Apply for OS/390

Enter the following MVS STOP command to stop the Apply for OS/390 program:

>>-P--jobname--------------------------------------------------><

Rules for index types

You can avoid deadlock and timeout problems in your applications if you specify TYPE 2 indexes, because TYPE 2 indexes do not lock index pages. TYPE 2 indexes also enable you to use other functions, such as parallel-query central processor (CP) processing, improved partition independence, row locking, and the ability to read through locks. If you specify TYPE 2 indexes, all specifications of SUBPAGES are ignored, and an error message is issued.

If you do not specify an index type, the index type is determined as follows:

If the LOCKSIZE is ROW, the default index type is TYPE 2, regardless of the type specified on the installation panel DSNTIPE.
If the LOCKSIZE is not ROW, the default index type is the type specified in the field DEFAULT INDEX TYPE on the installation panel DSNTIPE. The default value for that field is TYPE 2.

Recommendations:

Specify TYPE 2 as the index type for all table indexes. For best performance, bind the Capture program and the Apply packages using isolation UR. When you specify isolation UR, all indexes on the control tables must be TYPE 2 indexes. If source views are being used in subscription sets, and the Apply package is bound using isolation UR, only TYPE 2 indexes over the source tables involved in the source view can be used by the Apply program.
For DB2 for OS/390 source servers, specify TYPE 2 on every generated CREATE INDEX statement and every CREATE INDEX statement in the DB2 Control Center file DPCNTL.MVS. (DRJA creates TYPE2 indexes automatically.)

Using the DB2 ODBC Catalog

The DB2 ODBC Catalog is designed to improve the performance of ODBC applications. The tables in the DB2 ODBC Catalog are prejoined and indexed to support faster catalog access for ODBC applications. IBM's ODBC driver also supports multiple views of the DB2 ODBC Catalog.

Support for use of the DB2 ODBC Catalog is provided by DB2 DataPropagator Version 5 and later. For information about Version 5 level support, see the IBM Replication Guide and Reference V5. Enhancements to the DB2 ODBC Catalog for DB2 DataPropagator Version 6 include:

Support for the SYSIBM.SYSROUTINES table
Support for the SQLProcedureColumns ODBC function call

You can eliminate data currency problems by using the DB2 ODBC Catalog tables. DB2 DataPropagator for OS/390 Version 6 can keep data in the DB2 ODBC Catalog synchronized with the contents of the real DB2 catalog table. The Capture program identifies log records that represent changes to the DB2 catalog and records these changed data records in a staging table. The Apply program replicates the changed data records to the DB2 ODBC Catalog tables.

This section describes how to implement the DB2 ODBC Catalog using the automatic mode. The automatic mode automatically replicates any DB2 Catalog changes to the DB2 ODBC Catalog tables.

Setting up the DB2 ODBC Catalog

The following section provides setup instructions needed to prepare your client and server to run your ODBC queries.

Setting up the workstation client

To use the entire DB2 ODBC Catalog, add the entry CLISCHEMA=CLISCHEM to the DB2CLI.INI file. To use your own set of views rather than the entire DB2 ODBC Catalog, add the entry CLISCHEMA=MYSCHEMA to the DB2CLI.INI file. The following example contains both statements.

[tstcli1x]
uid=userid
pwd=password
autocommit=0
TableType="'TABLE','VIEW','SYSTEM TABLE'"
 
[tstcli2x]
  Assuming dbalias2 is a database in DB2 for MVS
SchemaList="'OWNER1','OWNER2','CURRENT SQLID'"
 
[MyVeryLongDBALIASName]
dbalias=dbalias3
SysSchema=MYSCHEMA
 
[RDBD2205]
AUTOCOMMIT=1
LOBMAXCOLUMNSIZE=33554431
LONGDATACOMPAT=1
PWD=USRT006
UID=USRT006
DBALIAS=RDBD2205
CLISCHEMA=CLISCHEM
 
[RDBD2206]
AUTOCOMMIT=1
LOBMAXCOLUMNSIZE=33554431
LONGDATACOMPAT=1
PWD=USRT006
UID=USRT006
DBALIAS=RDBD2206
CLISCHEMA=MYSCHEMA

You must define views for all the DB2 ODBC Catalog tables when you use your own schema. See Table 26 for the list of the DB2 ODBC Catalog tables for which you must define a view. Use the following VIEW MYSCHEMA statement to define the DB2 ODBC Catalog views on CLISCHEM.table_name ODBC tables.

CREATE VIEW MYSCHEMA.table_name FROM CLISCHEM.table_name
 where TABLE_SCHEM=MYUSER

Where table_name is one of DB2 ODBC Catalog table names.

Setting up the server

To set up the server, define the following control information for replication:

Create the DB2 DataPropagator for OS/390 control tables, if they do not already exist.
1. Review the header portion of the ASNL2CN6.SQL file and customize the table spaces according to your site requirements.
2. Connect to the OS/390 RDB that contains the catalog from which you want to create a new DB2 ODBC Catalog.
3. Run the ASNL2CN6.SQL file from either the client or the OS/390 server.
Create the source, subscription control, and table space information for the DB2 ODBC Catalog.
1. Review the header portion of the ASNL2SY6.SQL, ASNL2RE6.SQL, and ASNL2SU6.SQL files and customize the table spaces according to your site requirements.
2. Replace all occurrences of SRCE in the ASNL2SU6.SQL file with the OS/390 RDB name. You can also define additional views on the predefined subscriptions to further qualify the subscriptions.
3. Connect to the OS/390 RDB that contains the catalog from which you want to create a new DB2 ODBC Catalog.
4. Run the ASNL2SY6.SQL, ASNL2RE6.SQL, and ASNL2SU6.SQL files from either the client or the OS/390 server.
Start the Capture and Apply programs on OS/390. Starting the Capture and Apply programs populates the ODBC Catalog on OS/390.

DB2 ODBC Catalog tables

Table 26 lists the ODBC function calls that are supported by the DB2 ODBC Catalog and explains how the function calls are implemented by DB2 Universal Database for OS/390 Version 7.

Table 26. ODBC function calls

ODBC function call ODBC catalog tables
SQLColumns
The SELECT command is issued against preformatted data stored in CLISCHEM.COLUMNS.
This call is implemented using the source table SYSIBM.SYSCOLUMNS.
SQLColumnPrivileges
The SELECT command is issued against prejoined data stored in CLISCHEM.COLUMNPRIVILEGES.
This call is implemented with source tables SYSIBM.SYSCOLUMNS, SYSIBM.SYSTABAUTH, and SYSIBM.SYSCOLAUTH.
SQLForeignKeys
The SELECT command is issued against prejoined data stored in CLISCHEM.FOREIGNKEYS.
This call is implemented with source tables SYSIBM.SYSRELS, SYSIBM.SYSFOREIGNKEYS, and SYSIBM.SYSCOLUMNS.
SQLPrimaryKeys
The SELECT command is issued against the primary keys stored in CLISCHEM.PRIMARYKEYS.
This call is implemented with the source table SYSIBM.SYSCOLUMNS.
SQLProcedures
The SELECT command is issued against CLISCHEM.PROCEDURES, which contains only the columns required by the SQLProcedures function.
This call is implemented with source table SYSIBM.SYSROUTINES.
SQLSpecialColumns
The SELECT command is issued against prejoined data stored in CLISCHEM.SPECIALCOLUMNS.
This call is implemented with source tables SYSIBM.SYSCOLUMNS, SYSIBM.SYSKEYS, and SYSIBM.SYSINDEXES.
SQLTablesPrivileges
The SELECT command is issued against CLISCHEM.TABLEPRIVILEGES.
This call is implemented with source table SYSIBM.SYSTABAUTH.
SQLTables
The SELECT command is issued against prejoined data stored in CLISCHEM.TABLES.
This call is implemented with source table SYSIBM.SYSTABLES.
SQLStatistics
The SELECT command is issued against prejoined data stored in CLISCHEM.TSTATISTICS.
This call is implemented with source tables SYSIBM.SYSTABLES, SYSIBM.SYSINDEXES, and SYSIBM.SYSKEYS.
SQLProcedureColumns
The SELECT command is issued against prejoined data stored in CLISCHEM.PROCEDURECOLUMNS.
This call is implemented with source tables SYSIBM.SYSROUTINES and SYSIBM.SYSPARMS.

ODBC function call	ODBC catalog tables
SQLColumns	The SELECT command is issued against preformatted data stored in CLISCHEM.COLUMNS. This call is implemented using the source table SYSIBM.SYSCOLUMNS.
SQLColumnPrivileges	The SELECT command is issued against prejoined data stored in CLISCHEM.COLUMNPRIVILEGES. This call is implemented with source tables SYSIBM.SYSCOLUMNS, SYSIBM.SYSTABAUTH, and SYSIBM.SYSCOLAUTH.
SQLForeignKeys	The SELECT command is issued against prejoined data stored in CLISCHEM.FOREIGNKEYS. This call is implemented with source tables SYSIBM.SYSRELS, SYSIBM.SYSFOREIGNKEYS, and SYSIBM.SYSCOLUMNS.
SQLPrimaryKeys	The SELECT command is issued against the primary keys stored in CLISCHEM.PRIMARYKEYS. This call is implemented with the source table SYSIBM.SYSCOLUMNS.
SQLProcedures	The SELECT command is issued against CLISCHEM.PROCEDURES, which contains only the columns required by the SQLProcedures function. This call is implemented with source table SYSIBM.SYSROUTINES.
SQLSpecialColumns	The SELECT command is issued against prejoined data stored in CLISCHEM.SPECIALCOLUMNS. This call is implemented with source tables SYSIBM.SYSCOLUMNS, SYSIBM.SYSKEYS, and SYSIBM.SYSINDEXES.
SQLTablesPrivileges	The SELECT command is issued against CLISCHEM.TABLEPRIVILEGES. This call is implemented with source table SYSIBM.SYSTABAUTH.
SQLTables	The SELECT command is issued against prejoined data stored in CLISCHEM.TABLES. This call is implemented with source table SYSIBM.SYSTABLES.
SQLStatistics	The SELECT command is issued against prejoined data stored in CLISCHEM.TSTATISTICS. This call is implemented with source tables SYSIBM.SYSTABLES, SYSIBM.SYSINDEXES, and SYSIBM.SYSKEYS.
SQLProcedureColumns	The SELECT command is issued against prejoined data stored in CLISCHEM.PROCEDURECOLUMNS. This call is implemented with source tables SYSIBM.SYSROUTINES and SYSIBM.SYSPARMS.

[ Top of Page | Previous Page | Next Page ]