Writing Table Procedures W 13 Carla Pereira carla
Writing Table Procedures W 13 Carla Pereira carla. pereira@ca. com (c) 2002 Computer Associates International, Inc. (CA) All trademarks, trade names, service marks and logos herein belong to their respective referenced companies. CA confidential and proprietary information for CA internal use only. No unauthorized copying or distribution permitted. ca. com
Agenda § § § Table procedure overview Defining the table procedure Coding the table procedure Testing the table procedure SQL Quick Bridge for Advantage CAIDMS ca. com
What is a Table Procedure? § A type of table whose definition references an external program § This external program is called by the SQL engine to return rows of data § Requires the Advantage™ CA-IDMS®/DB Database SQL Option ca. com
Components § Table definition – Schema and name – Column definitions – External program name – Statistics § Program definition – Passed arguments – Structured code ca. com
Components (cont’d) § Reentrant or pseudo-reentrant program – COBOL II or VS COBOL with Amode=31 – LE 370 – PL/I – Assembler § Can use SQL Quick Bridge for Advantage CA-IDMS to generate COBOL source ca. com
When to Use a Table Procedure § Make complex structures, such as BOM, easier for the end user § Access non-SQL defined data which does not conform to SQL rules § Access all segments of a segmented database within a single SQL transaction § Access remote data ca. com
Accessing a Table Procedure § Use SQL DML statements § Reference it just like a view or table – SELECT E_ID FROM DEMOEMPL. TBLPROC; § Can join with other types of tables in a query § Security is the same as for a table – Grant and Revoke – SELECT, INSERT, UPDATE, DELETE, DEFINE privileges ca. com
Accessing a Table Procedure (cont’d) § When a reference is made to a table procedure – The SQL engine calls the associated program – Passes information to the program – Includes type of action required of the table procedure (“return next row”) – Table procedure performs request or returns an error ca. com
Steps to Create a Table Procedure § Define the table procedure § Design and write the table procedure (COBOL, PL/I or Assembler) § If necessary, define the program to an Advantage™ CA-IDMS®/DB Database system § Invoke the table procedure using SQL DML (SELECT, UPDATE, INSERT, DELETE) ca. com
Defining the Table Procedure CREATE TABLE PROCEDURE DEMOEMPL. TBLPROC ( E_ID UNSIGNED NUMERIC(4), E_NAME CHARACTER(25), E_ADDRESS CHARACTER(46) ) EXTERNAL NAME TPROCPGM ESTIMATED ROWS 1000 ESTIMATED IOS 100 USER MODE LOCAL WORK AREA 800 GLOBAL WORK AREA 800; ca. com
Coding the Table Procedure § § Calling arguments Scans Command operation codes Specifying parameter values ca. com
Calling Arguments § One for each parameter in the definition § One for each null indicator § Common arguments — same for all table procedures ca. com
Linkage Section — Sample Program TPROCPGM ca. com
Scans § Set of related operations performed on behalf of one or more SQL statements § For each scan, the table procedure is passed – Command code which invoked the scan – Operation code which indicates the type of action expected § All statements referencing same cursor are associated with same scan ca. com
Linkage Section — Sample Program TPROCPGM 01 01 SQL-COMMAND-CODE SQL-OP-CODE 88 OPEN-SCAN 88 NEXT-SCAN 88 CLOSE-SCAN PIC S 9(8) COMP SYNC. VALUE +12. VALUE +16. VALUE +20. ca. com
Operation Codes § § Open scan Next row Close scan Update row § § Delete row Insert row Suspend scan Resume scan ca. com
Command Codes Open Scan Next Row CLOSE DELETE Searched Close Scan Update Row Insert Row Suspend Scan X Y X FETCH X INSERT X X RESUME SELECT X X Y X SUSPEND UPDATE Searched Resume Scan X DELETE Positioned OPEN Delete Row X X Y X UPDATE Positioned Y X X = Called Once Y = Called 1 - n, loop with other Y ca. com
Example § A SELECT command will result in the following set of calls to the table procedure Open Scan Next Row (1 to n times) Close Scan § A searched UPDATE command will result in the following set of calls Open Scan Next Row (1 to n times) Update Row / Close Scan ca. com
Specifying Parameters § Treated like columns of a table § Specify parameters within – Column list of a SELECT or INSERT – SET clause of an UPDATE – ORDER BY clause of a SELECT – Search criteria of a WHERE clause ca. com
Specifying Parameters (cont’d) § Input parameters are values passed to the table procedure by the SQL engine § Can specify input parameters within the table procedure reference – By position – Keyword/value pairs SELECT * FROM EMP. ORG (MGR_ID = 7 , EMP_ID = 127) ca. com
Specifying Parameters (cont’d) § Parameter references on a WHERE clause – Passed to the table procedure if § They are part of an equality test § The equality test is not combined with OR § NOT does not precede the equality test – Filter the output of the table procedure SELECT * FROM EMP. ORG (MGR_ID = 7) WHERE EMP_ID = 127 ca. com
Specifying Parameters (cont’d) § Input parameters passed vary depending on operation code § Open scan (non-NULL parameters) – Selection criteria from WHERE – Parameter values in procedure reference – Default value if WITH DEFAULT § Update row – Previous “Next Row” values – Overlaid with SET value ca. com
Specifying Parameters (cont’d) § Insert row – From VALUES of INSERT or SELECT – Defaults or nulls § Other calls – Undefined input ca. com
Specifying Parameters (cont’d) § Output parameters are passed back from the table procedure to the SQL engine § Next row – Table procedure sets values for all parameters – Indicator variables if no value (set to -1) § SQLSTATE § Message-text (if desired) ca. com
SQLSTATE Value SQLCODE Value Description 00000 0 Request was successful 01 Hxx 1 Success with warning 02000 100 No more rows to be returned 38 xxx -4 Procedure detected an error ca. com
Work Areas § Local work area – Separate area for each scan – Preserved within a scan from one call to another – Use for database position, input parameters and more § Global work area – Shared across procedures and scans within a transaction – Subschema control – Has an associated key ca. com
Error Handling § Table procedure has two arguments to signal exception back to the SQL engine – Five-character SQLSTATE (translated to SQLCODE by the SQL engine) – Optional 80 -byte message area (embedded in standard DB message) § If table procedure signals an error, the SQL engine rolls out all database changes ca. com
Table Procedure Key § Define a key with CREATE KEY § Specifying key in WHERE clause influences join strategy § The SQL engine will use statistics defined for the KEY if the values supplied to the procedure match those defined on the KEY ca. com
TBLPROC Example SELECT E_ID, E_NAME FROM DEMOEMPL. TBLPROC WHERE E_ID = 371; ca. com
TBLPROC Example (cont’d) SQL Engine OPEN TBLPROC E_ID = 371 E_ID_I = 0 Command = select Operation = open Bind RU Ready area SQLSTATE=00000 ca. com
TBLPROC Example (cont’d) SQL Engine TBLPROC NEXT Obtain first record in area REC-->employee Command = select Operation = next Evaluate returned value with selection criteria (does not pass) E_ID = 0023 E_ID_I = 0 E_NAME =Katherine O’Hearn E_NAME_I=0 E_ADDRESS=12 East Speen St E_ADDRESS_I=0 SQLSTATE=00000 ca. com
TBLPROC Example (cont’d) SQL Engine NEXT TBLPROC Command = select Operation = next Obtain next record in area REC-->employee status = 0307 SQLSTATE=02000 ca. com
TBLPROC Example (cont’d) SQL Engine CLOSE TBLPROC Command = select Operation = close SQLSTATE=00000 ca. com
Testing § Run local mode – SYSIDMS DMLTRACE=ON – SYSIDMS PROCTRACE=ON – Print File or DISPLAY § CV Testing – PERFMON or other monitor for statistics – WRITE TO LOG or SNAP § Evaluate logical results ca. com
Miscellaneous § If table procedure issues only database requests, use BATCH protocol mode § If table procedure is executed within DC/UCF, it must be defined: – DCMT VARY PROGRAM – ADD PROGRAM system generation § COBOL working storage — use only for: – Constants the procedure will never change – Variables that are used within a single call ca. com
SQL Quick Bridge for Advantage CA-IDMS § Graphical User Interface (GUI) § Requires Advantage CA-IDMS Database Server Option § Generator – Definition – Program source – One direct path through database § Modify output as necessary ca. com
SQL Quick Bridge for Advantage CA-IDMS (cont’d) § Specify data sources – Schema, subschema – Records (begin with CALC or Index) – Key fields and sets – Additional data items on target record – Shared storage key ca. com
SQL Quick Bridge for Advantage CA-IDMS Entry Record Path Record Other Record Path Record Auxiliary Record Other Record Target Record ca. com
SQL Quick Bridge for Advantage CA-IDMS (cont’d) § Modify output as necessary – Navigation (BOM, multi-member, OM) – Combine segmented accesses – Variable portion of records ca. com
Session Summary § § § Table procedure overview Defining the table procedure Coding the table procedure Testing the table procedure SQL Quick Bridge for Advantage CA-IDMS ca. com
Questions & Answers ca. com
Session Evaluation Form After completing your session evaluation form. . . please place it in the basket at the back of the room. ca. com
Notes ca. com
- Slides: 43
