Converting COBOL to SQL PL Speaker Elwin Harrison
Converting COBOL to SQL PL Speaker Elwin Harrison A Major National Retailer Session Code: E 11 Wed, May 06, 2015 (10: 30 AM - 11: 30 AM)| Platform: Cross Platform
Converting COBOL to SQL PL Presentation Objectives • To provide the attendees with the understanding of converting COBOL programs into DB 2 SQL PL. • The attendees will learn what can be converted from COBOL into DB 2 SQL PL Native SQL Stored Procedures. Using the skills they learned during the seminar. • The attendees will learn how to create a DB 2 Native SQL Stored Procedure using IBM Data Studio, and add converted COBOL code to the stored procedure. • The attendees will learn how to use IBM Data Studio to deploy their DB 2 Native SQL Stored Procedure. • The attendees will learn how to use IBM Data Studio to perform unit testing of their stored procedures. 2
Converting COBOL to SQL PL Why convert COBOL to SQL PL • There are several reasons why you would want to convert COBOL to SQL PL. • Here are some of them: • • Reduce the work load on your mainframe by unloading some of it to the z. IIP specialty engine. Reduced cost of execution. When a DB 2 Native SQL stored procedure is executed on the z. IIP engine it reduces these items: • Reduced execution costs • Not mainframe utilization costs • Reduced charges for software utilization. 3
Converting COBOL to SQL PL Challenges COBOL SQL PL • The COBOL language is a mature and very robust language. • The COBOL language is a compiled language and executes at the machine level. • SQL PL is a new language and is still missing many of the constructs which give COBOL it robust stature. • SQL PL however is an interpreted language. 4
Converting COBOL to SQL PL Advantages • SQL PL when used in a DB 2 Native SQL Stored Procedure does not require the use of a Work Load Managed (WLM) address space for execution since it executes in the DBM 1 address spaces. This results in a savings of approximately 30, 000 for an initial execution of the stored procedure and an additional cost of 5000 instructions per SQL call. If the external stored procedure is coded to stay resident, then you do not incur the 30000 instruction cost for every invocation of the stored procedure. 5
Converting COBOL to SQL PL Short Comings • There is no INCLUDE capability so all code has to reside inside the SQL PL application or be in either a native SQL stored procedure or a native SQL user defined function that can be invoked by the application if you want the execution to be z. IIP eligible. If need the code reside in an External COBOL stored procedure. • There is limited array processing capability in DB 2 Version 11 for z. OS or none is you are in previous versions of DB 2 for z. OS. If you are using DB 2 LUW 10. 5 then there is a lot of array processing capabilities available. • There is no PERFORM construct, so if you need to do inline performs you will have to use one of the provided loop methods. 6
Converting COBOL to SQL PL Short Comings • If you need to do PERFORM routine to exit type processing there is no direct equivalent in SQL PL, however there are some tricks that you can use to mimic the functionality. 7
Converting COBOL to SQL PL COBOL Verbs not supported COBOL SQL PL • INITIALIZE • MOVE CORRESPONDING • Must use SET statement for each data element if group level. • Not available as a COBOL call however it does support an SQL CALL. • DISPLAY • CALL 8
Converting COBOL to SQL PL Conversion Getting Started COBOL SQL PL • Data names use a ‘-’ as a separator. • Limited to 28 characters. • Paragraph names can begin with a number: 000 -MAIN, and do not require an ending statement. • Data names use a ‘_’ as a separator. • Limited to 128 characters. • Paragraph names must begin with a character: A 000 MAIN: BEGIN, and do require and ending statement: END A 000 MAIN; 9
Converting COBOL to SQL PL COBOL Verb to SQL PL Conversion MOVE Verb COBOL SQL PL • MOVE var-a TO var-b. • MOVE var-a OF one TO var-a of two. • SET var_b = var_a; • The OF verb is not supported in SQL PL however you can code you variable AS: • DECLARE one_var_a • DECLARE one_var_b • SET one_var_b = one_var_a; 10
Converting COBOL to SQL PL COBOL Verb to SQL PL Conversion IF Verb COBOL SQL PL • IF var-a = var-b • END-IF. (Optional) • IF var-a = var-b AND var-c > var-d • IF var-a = var-b OR var-c = var-d • IF var-a = 1 or 2 or 3 • IF var-a = 1 or 3 or 5 • • IF var_a = var_b THEN END IF; IF (var_a = var_b) AND (var_c > var_D) THEN IF (var_a = var_b) OR (var_c = var_D) THEN IF var-a BETWEEN 1 AND 3 THEN • IF var-a IN (1, 3, 5) THEN 11
Converting COBOL to SQL PL COBOL Verb to SQL PL Conversion IF Verb COBOL SQL PL • IF var-a = var-b AND • var-c > var-d OR • var-a < var-b AND • var-c = var-d • IF (((var-a = var-b) AND • (var-c > var-d)) OR • ((var-a < var-b) AND • (var-c = var-d))) THEN 12
Converting COBOL to SQL PL COBOL Verb to SQL PL Conversion EVALUATE Verb COBOL SQL PL • EVALUATE var-a • SELECT CASE var_a • WHEN 2 • WHEN OTHER • END-EVALUATE • EVALUATE TRUE • WHEN var-a = 2 • WHEN OTHER • END-EVALUATE • WHEN 2 • THEN • ELSE • END SELECT; • SELECT CASE • WHEN var_a = 2 • THEN • ELSE • END SELECT; 13
Converting COBOL to SQL PL COBOL Verb to SQL PL Conversion EVALUATE Verb COBOL SQL PL • EVALUATE var-a • SELECT CASE • WHEN 2 • WHEN 3 • DO SOMETHING • WHEN var-a = 2 • WHEN OTHER • WHEN var_a IN (2, 3) • THEN DO SOMETHING • ELSE • END SELECT; • END-EVALUATE 14
Converting COBOL to SQL PL COBOL Verb to SQL PL Conversion EVALUATE Verb COBOL SQL PL • EVALUATE var-a • SELECT CASE • WHEN 1 • WHEN 2 • WHEN 3 • DO SOMETHING • WHEN OTHER • END-EVALUATE • WHEN var_a BETWEEN 1 AND 3 • THEN DO SOMETHING • ELSE • END SELECT; 15
Converting COBOL to SQL PL COBOL Verb to SQL PL Conversion COMPUTE Verb COBOL SQL PL • COMPUTE var-a = (var-b + 2) * 3. • COMPUTE var-a ROUNDED = (var-b + 2) / 3. • SET var_a = (var_b + 2) * 3; • SET ROUND((var_a = ((var_b + 2) * 3), 2); 16
Converting COBOL to SQL PL COBOL Verb to SQL PL Conversion ADD Verb COBOL SQL PL • ADD 5 TO var-a. • ADD 10 TO var-a GIVING varb. • SET var_a = var_a + 5; • SET var_b = var_1 + 10; 17
Converting COBOL to SQL PL COBOL Verb to SQL PL Conversion SUBTRACT Verb COBOL SQL PL • SUBTRACT 5 FROM var-a. • SUBTRACT 10 FROM var-a GIVING var-b. • SET var_a = var_a - 5; • SET var_b = var_1 - 10; 18
Converting COBOL to SQL PL COBOL Verb to SQL PL Conversion MULTIPLY Verb COBOL SQL PL • MULTIPLY 5 TO var-a. • MULTIPLY 10 TO var-a GIVING var-b. • SET var_a = var_a * 5; • SET var_b = var_1 * 10; 19
Converting COBOL to SQL PL COBOL Verb to SQL PL Conversion DIVIDE Verb COBOL SQL PL • DIVIDE 5 BY var-a. • DIVIDE 10 BY var-a GIVING var-b. • SET var_a = var_a / 5; • SET var_b = var_1 / 10; 20
Converting COBOL to SQL PL COBOL Verb to SQL PL Conversion PERFORM THRU Verb COBOL SQL PL • PERFORM 000 -MAIN • THRU 000 -MAIN-EXIT. • There is no equivalent code for this construct, however you can use a: • DECLARE CONTINUE HANDLER FOR SQLSTATE '75000' • BEGIN • A 000_MAIN: BEGIN • END A 000_MAIN; • END • EXECUTE BY • SIGNAL SQLSTATE '75000'; • These cannot be nested. 21
Converting COBOL to SQL PL COBOL Verb to SQL PL Conversion PERFORM VARYING UNTIL Verb COBOL SQL PL • PERFORM VARYING SUB 1 FROM 1 BY 1 UNTIL SUB 1 > 10 • WHILE (I <= 10) DO • DO SOMETHING • END-PERFORM. • SET I = I + 1; • DO SOMETHING; • END WHILE; 22
Converting COBOL to SQL PL COBOL Verb to SQL PL Conversion SET Verb COBOL SQL PL • 01 END-OF-FILE-SW PIC X(1). • • • DECLARE END_OF_FILE_SW CHAR(1); DECLARE EOF CHAR(1) DEFAULT ‘Y’; DECLARE NOT_EOF CHAR(1) DEFAULT ‘N’; • • SET END_OF_FILE_SW = EOF; IF END_OF_FILE_SW = EOF THEN • DO SOMETHING END IF; • • 88 EOF 88 NOT-EOF • SET EOF = TRUE. • IF EOF • DO SOMETHING • END-IF. VALUE ‘Y’. VALUE ‘N’. • 23
Converting COBOL to SQL PL COBOL Verb to SQL PL Conversion STRING Verb COBOL SQL PL • STRING var-a INTO var-b DELIMITED BY SIZE. • SET var_b = var_b || var_a; • SET var_b = var_b || RTRIM(var_a); 24
Converting COBOL to SQL PL COBOL Verb to SQL PL Conversion UNSTRING Verb COBOL SQL PL • 01 var-c. • • • 05 var-b PIC X(3). • 05 var-d PIC X(5). • 01 var-a PIC X(3). • 01 var_e PIC X(5) • UNSTRING var-b INTO var-a, DECLARE var_a CHAR(3); DECLARE var_e CHAR(3); DECLARE var_b CHAR(8); SET var_A = SUBSTRING(var_b, 1, 3); • SET var_e = SUBSTRING(var_b, 4, 5); 25
Converting COBOL to SQL PL COBOL Verb to SQL PL Conversion CALL Verb COBOL SQL PL • CALL program-a using parm 1. • Not possible using SQL PL. • However you can convert the called program_a, either to a DB 2 Native SQL stored procedure or some external stored procedure, and then replace the call with an: • EXEC SQL • CALL program_a parm 1; • END EXEC 26
Converting COBOL to SQL PL COBOL Verb to SQL PL Conversion IF Verb COBOL SQL PL 27
Converting COBOL to SQL PL COBOL Verb to SQL PL Conversion IF Verb COBOL SQL PL 28
Elwin Harrison A Major National Retailer Elwin. Harrison@yahoo. com Session: E 11 Title: Converting COBOL to SQL PL Please fill out your session evaluation before leaving!
- Slides: 29