还是那只鱼-newcoin's blog

DB2 Catalog

Q281) Which DB2 catalog tables are used to produce a list of table column by table for all tables in a database?

A281) The catalog tables to use are the SYSIBM.SYSTABLES and the SYSIBM.SYSCOLUMNS.

Q282) Which catalog tables contain authorization information?

A282) The SYSIBM table that contain authorization information are SYSCOLAUTH, SYSDBAUTH, SYSPLANAUTH, SYSTABAUTH and SYSUSERAUTH.

Q283) Which catalog table stores referential constraints?

A283) The SYSIBM.SYSRELS table.（文章来源：http://www.newcoin.info）

DB2 Directory

Q284) What Utility is used to migrate DB2 from one release to the next?

A284) The DUMPCAT Utility.

Q285) How would one remove old reorg information from the DB2 catalog?

A285) Run the MODIFY RECOVERY Utility.

Q286) What happens to a tablespace when its recovery infromation has been removed and a full recovery is no longer possible?

A286) The tablespace is put into copy pending status.

Q287) Where is the access path logic created by the DB2 Optimizer stored?

A287) The access path logic is stored as skeleton cursor tables in the SCT02 Directory table.

Q288) When is the skeleton cursor table created?

A288) During the execution of the BIND PLAN command.

Q289) How does one remove entries from the SCT02 table?

A289) Run the FREE PLAN command.

Q290) When one binds a PACKAGE ( of a plan ) what package information is stored and where it is stored?

A290) The access path information for the PACKAGE is stored as skeleton package tables in the SPT01 table.

Q291) Where besides the DB2 catalog is database object information stored by DB2?

A291) DB2 also stores information about DB2 objects as database descriptors (DBDs) in the DBD Directory table.

Q292) Can you access the DB2 Directory table using SQL?

A292) No. These tables are exclusively accessed by internal DB2 processes.

DB2 Commands

Q293) Which DB2 command is used to retrieve environmental information?

A293) The DISPLAY command can return the following environmental data: DATABASE info, RLIMIT info, THREAD info, TRACE info, and UTILITY info.

Q294) Which command is issued to establish the Boot Strap Data Set after an I/O failure?

A294) The DBA would issue a RECOVER BSDS command.

Q295) How is the status of a utility reset after it has been stopped by DB2 ?

A295) By issuing the START RLIMIT command.

Q296) How can one determine the status of a tablespace?

A296) By using the DISPLAY DATABASE command.

The following is the checklist to complete a DB2 batch or on-line program….

Batch DB2 COBOL program….（文章来源：http://www.newcoin.info）

1. If the program is main program it should have both DBB and DPK components.

2. If the program is linked (called) program it should have only DPK component. But the package generated should be binded in Calling program DBB component.

For example the DBB component looks like ….

BIND PLAN(????????) – **********>>> ENTER PLAN NAME

PKLIST(SEALAND.????????, – **********>>> ENTER MEMBER NAME

SEALAND.????????, – **********>>> (MULTIPLE MEMBERS

SEALAND.????????) – **********>>> FOR EACH PLAN)

QUALIFIER(TEST) – **********>>> MUST ALWAYS BE TEST

OWNER(????) – **********>>> ENTER YOUR TSO ID

ACTION(REPLACE) -

RETAIN -

VALIDATE(BIND) -

ISOLATION(CS) -

FLAG(I) -

ACQUIRE(USE) -

RELEASE(COMMIT) -

EXPLAIN(YES)

*********************************************************************

* THIS IS A SAMPLE DBB CARD FOR DB2 PACKAGING *

* *

* IF MULTIPLE MEMBERS ARE ENTERED IN PKLIST, *

* THERE MUST BE A DPK CARD FOR EACH ONE. *

*********************************************************************

The following is the format of the DPK card….

BIND PACKAGE(SEALAND) -

MEMBER(????????) – ***********>>> ENTER MEMBER NAME

VALIDATE(BIND) -

OWNER(????) – ***********>>> ENTER YOUR TSO ID

EXPLAIN(NO) -

QUALIFIER(TEST) ***********>>> MUST ALWAYS BE TEST

***********************************************************************

* *

* THIS IS A SAMPLE DPK CARD FOR DB2 PACKAGING *

* *

* *

**************

*********************************************************

3. As we are all aware that RCT is a concept of CICS. So batch DB2 program will not have any RCT entry.

4. To run this DB2 program the following the is the model JCL…

//TESTXXX JOB (AAAA),’ACCOUNTS PAYABLE’,CLASS=A,

// USER=XXXX,MSGCLASS=H,REGION=4096K

/*JOBPARM SYSAFF=B158

//JOBLIB DD DSN=TEST.JOBLIB,DISP=SHR

/*

//STEP010 EXEC PGM=IKJEFT01,DYNAMNBR=20

//STEPLIB DD DSN=DB2T.DSNEXIT,DISP=SHR

// DD DSN=DB2T.DSNLOAD,DISP=SHR

// DD DSN=TEST.JOBLIB,DISP=SHR

//INPUT1 DD DSN=XYZ.ABC.DBF,DISP=SHR

//OUTPUT1 DD DSN=XYZ.BCD.LEY,

// DISP=(NEW,CATLG,DELETE),

// DCB=(RECFM=FB,LRECL=122,BLKSIZE=2440),UNIT=SYSDA,

// SPACE=(CYL,(10,2),RLSE)

//SYSPRINT DD SYSOUT=*

//SYSTSPRT DD SYSOUT=*

//SYSOUT DD SYSOUT=*

//SYSERR DD SYSOUT=*

//SYSDUMP DD SYSOUT=*

//TESTDUMP DD SYSOUT=*

//SYSMSG DD SYSOUT=*

//SYSTSIN DD *

DSN SYSTEM(DB2T)

RUN PROG(TESTPROG) PLAN(TESTPLAN)

END

//*

As shown in above JCL program IKJEFT01 is used to run DB2 program (TESTPROG in our example).

In the above JCL program name and plan names are specified in SYSTSIN dataset as in-stream data.

The following is the check list to complete CICS(on-line) DB2 program…

1. If the program is main program it should have both DBB and DPK components.

2. If the program is LINKed, XCTLed program it should have only DPK component. But the package generated

should be binded in Calling program DBB component.

3. RCT entry has to be created if the program is main program(which consists of TRANSID). If the program is

LINKed or XCTLed it need not have a RCT entry. RCT entry basically used to attach CICS system to DB2

system.

4. No JCL business here because it is on-line.

5. The DBB and DPK s shown for batch program are also applicable to this.

1. What does SQLCODE +100 means

2. What does the SQLCODE +818 means

3. What are the pars of a SELECT statement

4. What is DB2 etc….,