DB2 connection in CICS TS 최종 - DBGuide.net · 2008-11-10 · 1 shows CICS using a thread to...

IBM Confidential

The Best Reliable Partner for High Availability

© Copyright IBM Corporation 2008

DB2 Connection in CICS TS

최지원부장

[email protected]

**‘2008 하반기효과적인시스템관리를위한기술세미나

Maintenance Technical Support & Services Global Technology Services, IBM

IBM Confidential1


© IBM Corporation 2008

Agenda

I. Overview of DB2 TermsI. Overview of DB2 Terms

II. DB2 Connection DefinitionsII. DB2 Connection Definitions

IV. SecurityIV. Security

III. Thread ReuseIII. Thread Reuse

V. Open Transaction EnvironmentV. Open Transaction Environment

IBM Confidential2



DB2 Connections

ConnectionAddress Space TCB identifies itself to DB2 for Services

ThreadA Bi-directional path between a user TCB and DB2 resources

PlanSQL Access Strategy used by the thread

IBM Confidential3



CICS – DB2

IBM Confidential4



CICS DB2 Attachment Facility Provides

Application program InterfaceEXEC SQL statements

Attachment commandsDSNC and CEMT

CICS DB2 connection characteristics are defined by RDONumber and types of threadsPlanname selection criteriaSecurity

CICS connects to a SINGLE DB2 SubsystemDB2 data sharing group support is also an option

CICS DB2 statistics and problem determination supportDSNC DISPLAY STAT, DFH0STAT AND DFHSTUPEDF, Messages, Abend Codes, Standard CICS Dump support

CICS TS V2.3 supports DB2 V5.1 or later

IBM Confidential5



CICS – DB2 INTERFACE

IBM Confidential6



DB2 Connection Definitions

DB2CONNGlobal attributes of connection to a named DB2 systemPOOL and COMMAND thread attributesOnly one can be installed at one time

DB2ENTRYTransaction Id with thread and plan selection requirementsGrouping transactions via wildcard symbols (* and + )Installed after DB2CONN

DB2TRANAdditional transaction Id relating to DB2ENTRYGrouping transactions via wildcard symbols also supported

IBM Confidential7



Define Panel for DB2CONN (1)

DB2CONN ==>

GROUP ==>

DB2ID ==>

PURGECYCLE ==> 00,30 0-59

SIGNID ==>

STANDBYMODE ==> RECONNECT Reconnect | Connect | Noconnect

TCBLIMIT ==> 12 4-2000

IBM Confidential8



Define Panel for DB2CONN (2)

POOL THREAD ATTRIBUTES

PLAN ==>

PLANEXITNAME ==>

PRIORITY ==> HIGH | EQUAL | LOW

THREADLIMIT ==> 0005 3-2000

THREADWAIT ==> YES YES | NO

COMMAND THREAD ATTRIBUTES

COMTHREADLIM ==> 0001

IBM Confidential9



Define Panel for DB2ENTRY

DB2ENTRY ==>

GROUP ==>

TRANSID ==>

PLAN ==>

PLANEXITNAME ==>

PROTECTNUM ==> 0000

THREAD LIMIT ==> 0000 0-2000

THREADWAIT ==> POOL Pool | YesNo

IBM Confidential10



Define Panel for DB2TRAN

DB2TRAN ==>

GROUP ==>

DESCRIPTION ==>

ENTRY ==>

TRANSID ==>

IBM Confidential11



Thread Recommendations

TRANSACTION CLASSControlled

ProtectHigh Volume

EntryMedium Volume

PoolLow Volume

Thread TypeTransactionCharacteristics

IBM Confidential12



Getting a thread

A task with SQL requests canreuse an existing thread that another task used previouslyor allocate a new thread.

In order to reuse a thread1. both tasks must be on the same DB2ENTRY2. both tasks must use the same plan.

A thread is released for reuseafter SYNCPOINTs for terminal driven transactions (& non terminal transactions if NONTERMREL(YES)) that do not have open cursors using the HOLD option and special registers are in the initial state.at EOT (end of task ) for all transactions.

IBM Confidential13



To Reuse or not reuse

When is thread reuse desirable?Primarily short & high volume transactionsCPU savings less significant for long transactions

When can thread reuse occur?THREADWAIT=YESor PROTECTNUM>0

IBM Confidential14



Protected Thread

DB2ENTRY:PROTECTNUM ==> 1THREADLIMIT ==> 1PURGECYCLE ==> (0,30)

IBM Confidential15



Display Plan

IBM Confidential16



DISPLAY STATISTICS

DSNC DISPLAY STATDSNC DISP STAT command shows limited stats for each DB2ENTRY

•Counters can be reset with CICS statistics commands

More comprehensive stats available via standard CICS interfaces

IBM Confidential17



CICS-DB2 Statistics

IBM Confidential18



CICS – DB2 Security

IBM Confidential19



Avoiding sign on

Avoiding signons where ever possible will improve the performance of transactions within a DB2ENTRY

Sign on always occurs when a thread is used for the first time. A thread is signed on again when the thread is reused and any of thefollowing occur:

Primary authorization ID 가변경될경우• Best choice : AUTHID(string) or AUTHTYPE(SIGN).

• Worst choice : AUTHTYPE(TERM) or AUTHTYPE(USERID).

ACCOUNTING RECORD 가새로생성될경우• 동일 Task 내에서도 Accountrec(uow) 에의해 Accounting record 가두개이상만들어지는경우

• TXID 가변경된경우

Partial sign on :Signons done for accounting purposes only are called partial signons. Whereas DSNC DISPLAY STATISICS only has an AUTHScolumn which will include both full and partial signons, the enhanced CICS-DB2 statistics available via DFH0STAT or DFHSTUP will show the a Signon count (ie full and partial signons) and a partial signon count.

IBM Confidential20



More Parameters

Correlate DB2 and CICS Monitor RecordsDB2CONN and DB2ENTRYACCOUNTTREC( NONE | TASK | TXID |UOW )

Unsolicited Attachment MessagesDB2CONNMSGQUEUE( CDB2 | TD queue name)

Connection Failure to DB2DB2CONNCONNECTIONERROR( SQLCODE | ABEND )

-923 AEY9

Create Thread ErrorDB2CONN

THREADERROR( N906D | N906 | ABEND )

IBM Confidential21



Open Transaction Environment

IBM Confidential22



Thread TCB in OTE

IBM Confidential23



Notes

1 shows CICS using a thread to access DB2 in the open transaction environment. The CICS DB2 task-related user exit has been invoked by the Resource Manager Interface (RMI), and it is operating on an open TCB. The CICS DB2 attachment facility has associated a CSUB and a DB2 connection control block with the open TCB. The DB2 connection control block has a thread into DB2. The plan associated with the thread is held in DB2. Situation

2 shows a thread that is not currently in use, but is protected. The CSUB and DB2 connection control block are still linked to each other and have a thread, but no open TCB is attached to them. The thread is available for reuse. Situation

3 shows an assembly that is left after a thread was terminated. The CSUB and DB2 connection control block are available for reuse. They need a new thread. Situation

4 shows open TCBs that are available for reuse. The CICS DB2 attachment facility can use these open TCBs and associate CSUB and DB2

IBM Confidential24



OTE in CICS TS 2.2

QUASIRENT applications with DB2 V6 and higherSame amount of TCB switching occursSwitches between QR and the L8 TCB rather than QR and a CICS-DB2 subtaskSome customers experienced performance problems and cpu increases

• Apply apars PQ67351, PQ72112, PQ75405 and PQ78987

THREADSAFE applications with DB2 V6 and higherReturn to the application on the 'L8' open TCBApplication logic run on open TCBNo TCB switch for next DB2 requestCICS requests may force switch back to QR or be run on the open TCB

• Depend on whether API is defined as threadsafe

IBM Confidential25



CICS – DB2 TCB switching prior to CICS TS 2.2

application

EXEC CICS

EXEC SQL

EXEC SQL

EXEC CICS

EXEC CICS

EXEC SQL

EXEC SQL

EXEC CICS

RETURN

DB2 Attach Code

QR TCB DB2 Attach TCB

IBM Confidential26



TCB switching for a threadsafe application

application

EXEC CICS

Non Threadsafe

EXEC CICS

application andDB2 Attach code

EXEC SQLEXEC SQL

Threadsafe EXEC CICS

EXEC SQLEXEC SQL

EXEC CICS RETURN

QR TCB Open TCB

IBM Confidential27



Global User Exits and OTE

Global User Exits (GLUEs) should be made THREADSAFE else unwanted TCB switching will occur

CICS will be forced to switch to QR TCB from an open TCB to invoke a non threadsafeGLUE

Particularly important for XRMIIN and XRMIOUT exits else CICS-DB2 benefits will be lost.

eg Apply Omegamon fix QC26070

Use DFH0STAT statistics programGlobal user exits report shows which programs are enabled at which exit points and whether they are defined as threadsafe

New XPI ENQUEUE and DEQUEUE function provided

Make exit threadsafe. Define exit program to CICS as threadsafe

IBM Confidential28



Changes to CICS – DB2 Externals

TCBLIMIT in DB2CONN definitionSpecifies the maximum number of TCBs that can be used to process DB2 requestsWhen connected to DB2 5.1 or below, the Attach creates TCBs up to the limit of TCBLIMITWhen connected to DB2 6.1 or above, CICS creates TCBs (up to a limit of MAXOPENTCBs in the SIT).

• TCBLIMIT specifies how many of these may be used to access DB2.

MAXOPENTCBS should always be equal or greater than TCBLIMIT

With TRANISO: MAXOPENTCBS should also be equal or greater than MXT.Apply apar PQ75405

IBM Confidential29



A reminder about Pre-OTE’s accounting…

IBM Confidential30



Accounting in an OTE environment ( Threadsafe )

Class-2 CPU = 3+7

IBM Confidential31



Accounting in an OTE environment

DB2 Class 1 time will be included in the CICS cpu time

L8 cpu time can be greater than DB2 class 1 timeMay also contain thread create/termination time.If application is threadsafe ...

• will contain cpu time spent in application• QR cpu time will decrease

DB2WAIT field will be zerorepresents elapsed time spent waiting for a DB2 request to complete.with OTE there is no CICS dispatcher wait for a subtask.

Can be large difference between DB2 Class 1 and Class 2 cpu timesCICS RMI code and threadsafe application codeCICS tracing

IBM Confidential32



Support for Group Attach

IBM Confidential33




DB2GROUPID parameter added to DB2CONN and INQUIRE/SET DB2CONN commands

Mutually exclusive to DB2ID parameterCICS will connect to any member of the group

• DB2 searches list of subsystems defined to this MVS

RESYNCMEMBER(YES¦NO) parameter added to DB2CONNYES (default) means if CICS thinks indoubts are outstanding then ignore group attach and attach to the specific DB2 member last used

NO means use group attach regardless of indoubtsCICS will nevertheless try a specific attach to the last used group member first. If that fails it will use group attach to connect to any group member.

IBM Confidential34




When connected using group attach:DB2GROUPID shows the 4 character groupidDB2ID shows the 4 character SSID of the member connected to

• DB2ID will be blanked out when CICS & DB2 disconnect

When connected without group attach:DB2GROUPID will be blankDB2ID shows the 4 character SSID of the DB2 connected to

• DB2ID is not blanked out when CICS & DB2 disconnect

IBM Confidential35




DSNC STRT yyyy or CEMT/EXEC CICS SET DB2CONN DB2ID(yyyy)yyyy specifies a specific DB2ID, not groupidforces non group attach, blanks out DB2GROUPID

INITPARM=(DFHD2INI='yyyy')yyyy specifies a specific DB2ID, not groupidforces non group attach, blanks out DB2GROUPIDRemove INITPARM setting if group attach required

DB2 V7 is a prerequisite, new DB2 group attach function:Wait for any member of the group to initialiseNew Connect types for CICSRequires maintenance to DB2 V7 ERLY code in LPA & MVS IPL

IBM Confidential36



Tuning Objectives (1)

Optimize the number of threads in the connection.

The total number of threads in the connection, and the number of threads for each dedicated entry and the pool must be optimized.

CTHREAD = TSO/BATCH + TCBLIMIT(DB2CONN)

Threads 수가너무많을경우 : TCB dispatch 를위한 CPU time 증가,

Plan, data, control block 을위한 storage 증가

Thread 수가 너무적을경우 : Response Time 감소

Optimize the assignment and reuse of threads.Tran Group 을하나의 Entry Thread 로지정

Reusing threads 는 thread creation and termination, plan allocation, authorization check

과정을 Skip 함으로써 CPU 절감됨.

Long running transactions 은 transaction classes 로통제하는것이좋다.

IBM Confidential37



Tuning Objectives (2)

Choose the priority assigned to the subtask thread TCBs, using the PRIORITY parameter.

Choose the best authorization strategy to avoid or minimize the process of signonby each thread.

Minimize the number of DB2ENTRYs.Use wildcarding to combine appropriate transactions in an entry.

Allow low use transactions to default to the pool.

Review StatisticsDSNC DISPLAY STAT

DFH0STAT and DFHSTUP

IBM Confidential38



감사합니다.감사합니다.Q & A

DB2 connection in CICS TS 최종 - DBGuide.net · 2008-11-10 · 1 shows CICS using a thread to...

Documents

Transcript of DB2 connection in CICS TS 최종 - DBGuide.net · 2008-11-10 · 1 shows CICS using a thread to...