1 / 58

DB2

DB2. Session Plan. SPUFI Hands On Introduction to Embedded SQL DCLGEN Hands On SQLCA Copy Book and its Use Single Row Manipulation Multiple Row Manipulation Cursors. SPUFI SQL Processor Using File Input. EMBEDDED SQL. Embedded SQL.

bena
Télécharger la présentation

DB2

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. DB2

  2. Session Plan • SPUFI Hands On • Introduction to Embedded SQL • DCLGEN Hands On • SQLCA Copy Book and its Use • Single Row Manipulation • Multiple Row Manipulation • Cursors

  3. SPUFI SQL Processor Using File Input

  4. EMBEDDED SQL

  5. Embedded SQL • Embedded SQL refers to the use of standard SQL statements within a high-level language program such as COBOL, PL1, C, PASCAL, etc,. • The high level language is called Host language • Embedded SQL statements are preprocessed by SQL Pre processor before the application program is compiled

  6. Embedded SQL • Embedded SQL syntax is almost same as the SQL syntax used in interactive mode. • The output of a query is directed to a pre-defined set of variables instead of the terminal. • These variables are defined in the host language and are referred to as the host variables. • An additional INTO clause is placed after the SQL SELECT statement.

  7. Types of SQL • Static SQL: The application programmer knows in advance the SQL statement completely. 2. Dynamic SQL: The application programmer is unaware of the SQL statement in advance. This requires dynamic compilation and binding.

  8. Host Variables(1 of 2) • Host variables are just like any other variable of the high level language • They are referred as host variables because they are used for receiving data from the table or inserting data to the table. • One must declare host variables for all values that are to be passed between the application program and DB2. • The host variables may appear anywhere in the WORKING-STORAGE SECTION. • The data types of the DB2 columns and corresponding host variables must be compatible.

  9. Host variables (2 of 2) • The host variables can not be group items, the only exception to this rule is the variable corresponding to VARCHAR. • The host variables may be grouped together in a host structure. • The host variables can be Redefined • The host variable cannot be Renamed.

  10. Declaring Host Variables • Need to declare in the working-storage section of your program EXEC SQL BEGIN DECLARE SECTIONEND-EXEC. 01 EMPLOYEE-REC. 03 EMP-NO PIC S9(4) COMP. 03 EMP-NAME PIC X(15). 03 EMP-ADDRESS. 49 EMP-ADDRESS-LEN PIC S9(4) COMP. 49 EMP-ADDRESS-TEXT PIC X(25). EXEC SQL END DECLARE SECTIONEND-EXEC.

  11. DCLGEN Generating copybook of host variables

  12. Using DCLGEN copybook EXEC SQLINCLUDE STUD627END-EXEC

  13. SQLCA • This is the communication area (a copybook containing some variables) through which DB2 passes the feedback of SQL execution to the program • In the working-storage section use WORKING-STORAGE SECTION. EXEC SQL INCLUDE SQLCA END-EXEC.

  14. SQLCA Copy Book (1 Of 3) 01 SQLCA. 03 SQLCAID PIC X(8). 03 SQLCABC PIC S9(9) COMP. 03 SQLCODE PIC S9(9) COMP VALUE 0. 03 SQLERRM. 49 SQLERRML PIC S9(4) COMP. 49 SQLERRMC PIC X(70). 03 SQLERRP PIC X(8). 03 SQLERRD OCCURS 6 PIC S9(9) COMP. 03 SQLWARN. 05 SQLWARN0 PIC X. 05 SQLWARN1 PIC X. 05 SQLWARN2 PIC X. 05 SQLWARN3 PIC X. 05 SQLWARN4 PIC X. 05 SQLWARN5 PIC X. 05 SQLWARN6 PIC X. 05 SQLWARN7 PIC X. 03 SQLSTATE PIC X(5)

  15. SQLCA Copy Book (2 of 3) 03 SQLCAID For Reading dumps.03 SQLCABC Length of SQLCA (136 bytes).03 SQLCODE = 0 (Successful) = +ve (Exceptional condition) = -ve (Failure)03 SQLERRM. 49 SQLERRML Error Message Length. 49 SQLERRMC Error Message Text.03 SQLERRP Info about internal Error.03 SQLERRD(1) Internal Error Code. 03 SQLERRD(2) Internal Error Code. 03 SQLERRD(3) Number of Rows affected by INSERT, UPDATE and DELETE. 03 SQLERRD(4) Estimate of resources needed by dynamic SQL statement. 03 SQLERRD(5) Info about dynamic SQL. 03 SQLERRD(6) Internal Error Code.

  16. SQLCA Copy Book (3 of 3) 05 SQLWARN0 ‘W’ if any other SQLWARNx fields are set to ‘W’. 05 SQLWARN1 ‘W’ indicates truncation of character strings. 05 SQLWARN2 ‘W’ indicates null values were ignored. Ex: AVG, COUNT. 05 SQLWARN3 ‘W’ indicates more columns than host variables. 05 SQLWARN4 ‘W’ indicates no WHERE clause for UPDATE or DELETE. 05 SQLWARN5 ‘W’ indicates SQL/DS statement. 05 SQLWARN6 ‘W’ for adjustment of DATE. 05 SQLWARN7 Reserved for Future use.

  17. Programming guidelines (1 of 2) • Every SQL statement must be coded between columns 12 and 72. • Every SQL statement must be delimited between EXEC SQL and END-EXEC. • All the tables that are used in a program are to be declared in the WORKING- STORAGE SECTION. This can be done using INCLUDE statement • All SQL statements other than INCLUDE and DECLARE TABLE must appear in PROCEDURE DIVISION. • Anything on a line within an SQL statement following two hyphens is treated as a comment. Even * can be coded in column 7 for comments.

  18. Single Row Manipulation (1 of 6) SELECT EXEC SQL SELECT EMPNO, SALARY INTO :WS-EMPNO, :WS-SALARY FROM EMMPLOYEE WHERE NAME = :WS-NAME END-EXEC Error Handling IF SQLCODE = 0 CONTINUE ELSE IF SQLCODE = -811 DISPLAY “MULTIPLE ROWS” ELSE PERFORM C9000-ERROR-PARA.

  19. Single Row Manipulation (2 of 6) INSERT EXEC SQL INSERT INTO EMPLOYEE (EMPNO, NAME, SALARY) VALUES (:WS-EMPNO, :WS-NAME, :WS-SALARY) END-EXEC Error Handling IF SQLCODE = 0 CONTINUE ELSE DISPLAY “UNIDENTIFIED ERROR OCCURRED”.

  20. Single Row Manipulation (3 of 6) UPDATE EXEC SQL UPDATE EMPLOYEE SET SALARY = :WS-SALARY WHERE EMPNO = 10878 END-EXEC Error Handling IF SQLCODE = 0 CONTINUE ELSE DISPLAY “UNIDENTIFIED ERROR OCCURRED”.

  21. Single Row Manipulation (4 of 6) DELETE EXEC SQL DELETE FROM EMPLOYEE WHERE EMPNO = :WS-EMPNO END-EXEC Error Handling IF SQLCODE = 0 CONTINUE ELSE DISPLAY “UNIDENTIFIED ERROR OCCURRED”.

  22. Single Row Manipulation (5 of 6) Selecting Nullable columns(Columns which can take NULL values) EXEC SQL SELECT NAME, SALARY INTO :WS-NAME, :WS-SALARY:WS-SALARY-IND FROM EMPLOYEE WHERE EMPNO = :WS-EMPNO END-EXEC Indicator variable values and their meanings (1) 0 Not Null (2) -1 Null (3) -2 Exceptional condition

  23. Single Row Manipulation (6 of 6) Inserting Null values to columns MOVE -1 TO WS-SALARY-IND EXEC SQL INSERT INTO EMPLOYEE(SALARY) VALUES:WS-SALARY:WS-SALARY-IND END-EXEC Error Handling IF SQLCODE = 0 CONTINUE ELSE DISPLAY “UNIDENTIFIED ERROR OCCURRED”.

  24. Cursors (1 of 7) • Are memory structures used to handle multiple row selections at a time. • Conceptually they is a results table used by DB2 to contain the multiple results of a query. • They are data structures which hold some/all the results of a query. • Are defined in the WORKING- STORAGE SECTION/ PROCEDURE DIVISION.

More Related