High-Performance Oracle Database Applications:Performance And Tuning For Oracle Database Connectivity Tools

Listing 8.5 pending.sql reports on any pending distributed transactions.

SET PAGESIZE 999;
SET FEEDBACK OFF;
SET WRAP ON;
COLUMN local_tran_id  format a22 HEADING 'Local Txn Id'
COLUMN global_tran_id format a50 HEADING 'Global Txn Id'
COLUMN state          format a16 HEADING 'State'
COLUMN mixed          format a5  HEADING 'Mixed'
COLUMN advice         format a5  HEADING 'Advice'

SELECT local_tran_id,global_tran_id,state,mixed,advice
FROM   dba_2pc_pending
ORDER  BY local_tran_id;

Establishing SQL*Net Sessions

On systems running SQL*Net version 2, the session script can be used to query the number of dedicated and shared servers on the system. For example, Listing 8.6 shows an SQL*Plus script to view all sessions.

Listing 8.6 session.sql displays all connected sessions.

SET ECHO OFF;
SET TERMOUT ON;
SET LINESIZE 80;
SET PAGESIZE 60;
SET NEWPAGE 0;
TTITLE "dbname Database|UNIX/Oracle Sessions";
SPOOL /tmp/session
SET HEADING OFF;
SELECT 'Sessions on database '||substr(name,1,8) FROM v$database;
SET HEADING ON;
SELECT
       substr(b.serial#,1,5) ser#,
       substr(b.machine,1,6) box,
       substr(b.username,1,10) username,
       substr(b.osuser,1,8) os_user,
       substr(b.program,1,30) program
FROM v$session b, v$process a
WHERE
b.paddr = a.addr
AND type='USER'
ORDER BY spid;
TTITLE OFF;
SET HEADING OFF;
SELECT 'To kill, enter SQLPLUS>  ALTER SYSTEM KILL SESSION',
''''||'SID, SER#'||''''||';' from dual;
SPOOL OFF;
[oracle]ram2: sqlx session
Wed Sep 14
                                                                 Page    1
                                                          ram2db Database
                                                     Sessions for SQL*Net
SERVER     Oracle user  O/S       User     Machine Program
---------  -----------  --------  ------  ------------------------------
DEDICATED  SYS          oracle    ram2    sqldba@ram2 (Pipe Two-Task)

DEDICATED  OPS$REDDY    reddy     ram2    runform30@ram2 (Pipe Two-Task)

DEDICATED  GLINT        lkorneke  ram2    sqlplus@ram2 (Pipe Two-Task)

DEDICATED  OPS$ORACLE   oracle    clt2    sqlplus@clt2 (TNS interface)

DEDICATED  OPS$JOKE     joke      ram2    ?  @ram2 (TCP Two-Task)

DEDICATED  OPS$WWRIGHT  wwright   ram2    runmenu50@ram2 (Pipe Two-Task)

DEDICATED  OPS$ORACLE   oracle    ensc    sqlplus@ensc (TCP Two-Task)

DEDICATED  SECTION125   OraUser           C:\PB3\PBSYS030.DLL

DEDICATED  OPS$ORACLE   oracle    ram2    sqlplus@ram2 (Pipe Two-Task)

DEDICATED  OPS$JSTARR   jstarr    ram2    sqlforms30@ram2 (Pipe Two-Task)

DEDICATED  OPS$WWRIGHT  wwright   ram2    RUN_USEX@ram2 (Pipe Two-Task)

12 rows selected.

Here we see each of the four types of SQL*Net connections:

• Pipe Two-Task—Used for internal tasks (SQLPLUS /).

• TNS Interface—Used when connection is made with a v2 service name (SQLPLUS /@ram2).

• TCP Two-Task—Used when connection is made with a v1 connect string (SQLPLUS /@t:ram2:ram2db).

• PC connection task—Denoted by the PC DLL name (C:\PB3\PBSYS030.DLL = initiated via PowerBuilder DLL).

While we can only gloss over the high points, sophisticated tools such as SQL*Net 2.0 require a great deal of knowledge and skill to use effectively. As systems continue to evolve into complex distributed networks, inter-database communications will become even more complex, requiring even more sophisticated tools. While object-orientation promises to make interdatabase communications simple, the DBA in the trenches will continue to struggle with implementing everyday distributed database communications.

Cross Database Connectivity With IBM Mainframes

A great deal of interest in database connectivity has resulted from the proliferation of companies that are choosing diverse database platforms. Database designers are working actively to develop bridges between the divergent database systems, and many tools are becoming available to assist with multidatabase connectivity.

We currently have three classes of methods for database connectivity:

• Transparency products that allow applications written for a database to run on another product

• Fourth generation languages that access multiple databases

• “Hook” products that allow exits to other databases

This very exciting area of technology is explored in detail in Chapter 9. Fortunately, we have some very simple ways to begin working with multiple databases. One start at database connectivity can be achieved in the batch environment. By embedding database commands from two databases into a single COBOL program, compile procedures can be developed to separately precompile each set of database statements, creating a single program which concurrently accesses two different databases. (See Figure 8.6.)

Figure 8.6 Interdatabase communications with mainframes.

In this example, a COBOL program was created to read DB2 tables and dynamically store them into an CA-IDMS database. The statements for DB2 and CA-IDMS remain as they would in a single database program, but a special compile procedure is set up to precompile each set of statements separately. The trick is to invoke the DB2 precompiler before the CA-IDMS pre-compiler. Because all DB2 commands are bracketed with EXEC-SQL .... END-EXEC, all DB2 commands will be processed before the CA-IDMS pre-compiler begins its job. Listing 8.7 shows some JCL that works well for compiling a COBOL program to simultaneously access DB2 and CA-IDMS.

Listing 8.7 A COBOL compiler.

//DB2IDMSC PROC  PROGRAM=,SYSTEM=PROD,DICT=LCPDICT
//***************************************************************
//*  THIS IS THE IDMS/DB2 COBOL COMPILER  (BATCH IDMS/DB2 COBOL)
//***************************************************************
//DB2 EXEC PGM=DSNHPC,PARM='HOST(COBOL),APOST,APOSTSQL,NOSOURCE,NOXREF'
//***************************************************************
//DBRMLIB  DD DSN=DB2.LCP.DBRMLIB(&PROGRAM),DISP=SHR
//SYSCIN   DD  DSN=&&DSNHOUT,DISP=(MOD,PASS),UNIT=SYSDA,
//             SPACE=(800,(50,50))
//SYSLIB   DD  DSN=DB2.LCP.DCLGNLIB,SUBSYS=LAM
//SYSPRINT DD  SYSOUT=*
//SYSTERM  DD  SYSOUT=*
//SYSUDUMP DD  SYSOUT=*
//SYSUT1   DD  SPACE=(800,(50,50),,,ROUND),UNIT=SYSDA
//SYSUT2   DD  SPACE=(800,(50,50),,,ROUND),UNIT=SYSDA
//***************************************************************
//DMLC EXEC  PGM=IDMSDMLC,REGION=1024K,PARM='DBNAME=&DICT',COND=(4,LT)
//***************************************************************
//STEPLIB  DD  DSN=LCP.IDMS.&SYSTEM..PRODLIB,DISP=SHR
//         DD  DSN=LCP.IDMS.&SYSTEM..CDMSLIB,DISP=SHR
//SYSLST   DD  DUMMY
//SYSPRINT DD  SYSOUT=*
//SYSCTL   DD  DSN=LCP.IDMS.&SYSTEM..SYSCTL,DISP=SHR
//SYSJRNL  DD  DUMMY
//SYSPCH   DD  DSN=&&WRK1WORK,UNIT=SYSDA,DISP=(NEW,PASS),
//             DCB=BLKSIZE=800,SPACE=(CYL,(5,1))
//SYSIPT   DD  DSN=&&DSNHOUT,DISP=(OLD,DELETE,DELETE)
//***************************************************************
//COMP    EXEC PGM=IKFCBL00,COND=(4,LT),
// PARM='&CPARM,&PAYPR,STA,LIB,DMAP,CLIST,APOST,NOSXREF,BUF=28672'
//***************************************************************
//STEPLIB  DD  DSN=SYS1.VSCOLIB,DISP=SHR
//SYSPRINT DD  SYSOUT=*
//SYSUDUMP DD  SYSOUT=*
//SYSLIN   DD  DSN=&&LOADSET,DISP=(MOD,PASS),UNIT=SYSDA,
//             SPACE=(800,(50,50),RLSE)
//SYSLIB   DD  DSN=ISD.COBOL.TEST.COPYLIB,DISP=SHR
//         DD  DSN=ISD.COBOL.COPYLIB,DISP=SHR
//SYSUT1   DD  SPACE=(800,(50,50),RLSE),UNIT=SYSDA
//SYSUT2   DD  SPACE=(800,(50,50),RLSE),UNIT=SYSDA
//SYSUT3   DD  SPACE=(800,(50,50),RLSE),UNIT=SYSDA
//SYSUT4   DD  SPACE=(800,(50,50),RLSE),UNIT=SYSDA
//SYSIN    DD  DSN=&&WRK1WORK,DISP=(OLD,DELETE,DELETE)
//***************************************************************
//LKED    EXEC PGM=IEWL,PARM='XREF,LIST,&LPARM',
//             COND=((12,LE,COMP),(4,LT,DB2))
//***************************************************************
//SYSPRINT DD  SYSOUT=*
//SYSUDUMP DD  SYSOUT=*
//SYSLIN   DD  DSN=&&LOADSET,DISP=(OLD,DELETE)
//         DD  DSN=LCP.IDMS.TEST.PROCLIB(IDMSCOB),DISP=SHR
//SYSUT1   DD  SPACE=(1024,(50,50)),UNIT=SYSDA
//SYSLIB   DD  DSN=SYS1.DB2.DSNLINK,DISP=SHR
//         DD  DSN=SYS1.VSCOLIB,DISP=SHR
//         DD  DSN=SYS1.VSCLLIB,DISP=SHR
//         DD  DSN=LCP.IDMS.&SYSTEM..CDMSLIB,DISP=SHR
//SYSLMOD  DD  DSN=LCP.IDMS.&SYSTEM..LINKLIB(&PROGRAM),DISP=SHR
//LIB      DD  DSN=LCP.IDMS.&SYSTEM..OBJLIB,DISP=SHR
//***************************************************************
//BIND  EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=((8,LT),(4,LT,DB2))
//***************************************************************
//SYSTSIN  DD DSN=ISD.TEST.PARMCARD(PCRDB21),DISP=SHR
//DSNTRACE DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSTSPRT DD SYSOUT=*
//SYSIN    DD DSN=LCP.IDMS.&SYSTEM..LINKLIB(&PROGRAM),DISP=SHR

The execution of the COBOL program is normally achieved by running it under the domain of the DB2 foreground processor. Listing 8.8 shows how the execution JCL for a DB2/IDMS COBOL program would appear.

Listing 8.8 The execution JCL for the COBOL program.

//GOFORIT  JOB (CARD)
//GOFORIT  EXEC PGM=IKJEFT01,DYNAMNBR=20
//***************************************************************
//SYSTSIN  DD *
   DSN SYSTEM    (DB2P)
   RUN PROG      (MYCOBOL) -
   LIB           ('MY.LINK.LIB')  -
   PLAN          (MYCOBOL)
//DSNTRACE DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSTSPRT DD SYSOUT=*
//STEPLIB  DD  DSN=MY.IDMS.LIBRARIES,DISP=SHR

Table of Contents