Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1791965
  • 博文数量: 335
  • 博客积分: 4690
  • 博客等级: 上校
  • 技术积分: 4341
  • 用 户 组: 普通用户
  • 注册时间: 2010-05-08 21:38
个人简介

无聊之人--除了技术,还是技术,你懂得

文章分类

全部博文(335)

文章存档

2016年(29)

2015年(18)

2014年(7)

2013年(86)

2012年(90)

2011年(105)

分类: 系统运维

2013-06-02 00:50:37

玩了一天,晚上静下心来写点博客,关于使用coboL 编写external procedure 
具体的步骤如下:
1WLM environment must be set up. OS协助
2. LE environment must be set up. OS协助(系统安装完成后都应该有)
3. The stored procedure must be defined to DB2. Note in particular that if the stored 
procedure is designed to return result sets, the maximum number of result sets that can 
be returned is specified in the definition. 
4. Develop the stored procedure. This includes the preparation of the stored procedure for 
execution, including binding a package if it contains SQL.
5. Grant the necessary privileges to the authorization ID of the user that executes the stored 
procedure.
6. Develop the calling application, if needed.
通过上面的步骤我们看出主要的开发步骤主要包括:
3,4,6 其它的都很简单的,下面举例说明
STEP1 :
比如我们想创建一个empAUDTS的procedure,首先在DB2中定义该procedure如下:

点击(此处)折叠或打开

  1. DROP PROCEDURE DEVL7083.EMPAUDTS #
  2. COMMIT#
  3. CREATE PROCEDURE DEVL7083.EMPAUDTS
  4. (
  5.  IN PEMPNO CHAR(6)
  6. ,IN POLDSALARY DEC(9,2)
  7. ,IN PNEWSALARY DEC(9,2)
  8. )
  9. RESULT SETS 0
  10. EXTERNAL NAME EMPAUDTS  需要特别注意的是这里,这里定义loadmodule的名字,即我们编写的程序的名字
  11. LANGUAGE COBOL
  12. PARAMETER STYLE GENERAL
  13. MODIFIES SQL DATA
  14. NO DBINFO
  15. WLM ENVIRONMENT DB2GDEC1   /wlm envionment 
  16. STAY RESIDENT NO
  17. COLLID DEVL7083
  18. PROGRAM TYPE SUB
  19. COMMIT ON RETURN NO #
  20. GRANT EXECUTE ON PROCEDURE DEVL7083.EMPAUDTS TO PUBLIC #
其实上面的这一步与其说是定义了一个procedure,不过说是关联了一个cobol程序,该程序执行的时候,会直接调用我们的load module
而load module 则是cobol的结果
STEP2: 
我们的cobol程序如下:

点击(此处)折叠或打开

  1. 000100*-----------------------------------------------------------------00010000
  2. 000200* EMPAUDTS - SAMPLE COBOL STORED PROCEDURE 00020016
  3. 000300* INVOKED BY A TRIGGER PASSING TRANSITION VARIABLES 00030000
  4. 000400* 00040000
  5. 000500* MODULE NAME = EMPAUDTS 00050016
  6. 000600* 00060000
  7. 000700* FUNCTION = THIS MODULE ACCEPTS AN EMPLOYEE NUMBER, 00070000
  8. 000800* OLD SALARY AND NEW SALARY AND DISPLAYS THEM. 00080000
  9. 000900* 00090016
  10. 001000*-----------------------------------------------------------------00100000
  11. 001100 IDENTIFICATION DIVISION. 00110000
  12. 001200*----------------------- 00120000
  13. 001300 PROGRAM-ID. EMPAUDTS. 00130016
  14. 001400/ 00140000
  15. 001500 ENVIRONMENT DIVISION. 00150000
  16. 001600*-------------------- 00160000
  17. 001700 CONFIGURATION SECTION. 00170000
  18. 001800 INPUT-OUTPUT SECTION. 00180000
  19. 001900 FILE-CONTROL. 00190000
  20. 002000 DATA DIVISION. 00200000
  21. 002100*------------- 00210000
  22. 002200 FILE SECTION. 00220000
  23. 002300/ 00230000
  24. 002400 WORKING-STORAGE SECTION. 00240000
  25. 002500*-----------------------------------------------------------------00250016
  26. 002600* WORKAREAS 00260000
  27. 002700*-----------------------------------------------------------------00270016
  28. 002800 01 WS-PARMAREA. 00280006
  29. 002900 02 WS-EMPNO PIC X(06). 00290007
  30. 003000 02 WS-OLDSALARY PIC S9(7)V99 COMP-3. 00300016
  31. 003100 02 WS-NEWSALARY PIC S9(7)V99 COMP-3. 00310007
  32. 003200 00320002
  33. 003300*-----------------------------------------------------------------00330016
  34. 003400* VARIABLES FOR ERROR-HANDLING 00340000
  35. 003500*-----------------------------------------------------------------00350016
  36. 003600 01 ERROR-MESSAGE. 00360000
  37. 003700 02 ERROR-LEN PIC S9(4) COMP VALUE +960. 00370000
  38. 003800 02 ERROR-TEXT PIC X(80) OCCURS 12 TIMES 00380000
  39. 003900 INDEXED BY ERROR-INDEX. 00390000
  40. 004000 77 ERROR-TEXT-LEN PIC S9(9) COMP VALUE +80. 00400000
  41. 004100 00410000
  42. 004200/ 00420000
  43. 004300*-----------------------------------------------------------------00430016
  44. 004400* SQLCA AND DCLGENS FOR TABLES 00440000
  45. 004500*-----------------------------------------------------------------00450016
  46. 004600 EXEC SQL INCLUDE SQLCA END-EXEC. 00460000
  47. 004700 EXEC SQL INCLUDE EMP END-EXEC. 00470016
  48. 004800 00480000
  49. 004900/ 00490000
  50. 005000 LINKAGE SECTION. 00500005
  51. 005100 01 PEMPNO PIC X(6). 00510012
  52. 005200 01 POLDSALARY PIC S9(7)V9(2) COMP-3. 00520012
  53. 005300 01 PNEWSALARY PIC S9(7)V9(2) COMP-3. 00530016
  54. 005400*-----------------------------------------------------------------00540016
  55. 005500* SQL CURSORS AND STATEMENTS 00550000
  56. 005600*-----------------------------------------------------------------00560016
  57. 005700 00570000
  58. 005800/ 00580000
  59. 005900 PROCEDURE DIVISION USING PEMPNO, POLDSALARY, PNEWSALARY. 00590016
  60. 006000 00600000
  61. 006100*-----------------------------------------------------------------00610016
  62. 006200* MAIN PROGRAM ROUTINE 00620000
  63. 006300*-----------------------------------------------------------------00630016
  64. 006400 MAINLINE. 00640000
  65. 006500 DISPLAY '++ START OF EMPAUDTS STARTING ++'. 00650016
  66. 006600 00660000
  67. 006700 EXEC SQL 00670002
  68. 006800 SET CURRENT SQLID = USER 00680016
  69. 006900 END-EXEC. 00690002
  70. 007000 00700002
  71. 007100 PERFORM 2000-PROCESS 00710000
  72. 007200 THRU 2000-EXIT. 00720000
  73. 007300 00730000
  74. 007400 DISPLAY '++ END OF EMPAUDTS ++'. 00740016
  75. 007500 00750000
  76. 007600 GOBACK. 00760000
  77. 007700/ 00770000
  78. 007800*-----------------------------------------------------------------00780016
  79. 007900* 2000-PROCESS 00790000
  80. 008000*-----------------------------------------------------------------00800016
  81. 008100 2000-PROCESS. 00810000
  82. 008200 MOVE PEMPNO TO WS-EMPNO. 00820015
  83. 008300 MOVE POLDSALARY TO WS-OLDSALARY. 00830016
  84. 008400 MOVE PNEWSALARY TO WS-NEWSALARY. 00840015
  85. 008500 00850005
  86. 008600 DISPLAY 'WS-EMPNO = ' WS-EMPNO. 00860016
  87. 008700 DISPLAY 'WS-OLDSALARY = ' WS-OLDSALARY. 00870016
  88. 008800 DISPLAY 'WS-NEWSALARY = ' WS-NEWSALARY. 00880016
  89. 008900 00890005
  90. 009000 2000-EXIT. 00900016
  91. 009100 EXIT. 00910000
  92. 009200/ 00920000
  93. 009300*-----------------------------------------------------------------00930016
  94. 009400* 9000-DBERROR - GET ERROR MESSAGE 00940000
  95. 009500*-----------------------------------------------------------------00950016
  96. 009600 9000-DBERROR. 00960000
  97. 009700 CALL 'DSNTIAR' USING SQLCA ERROR-MESSAGE ERROR-TEXT-LEN.00970000
  98. 009800 IF RETURN-CODE = ZERO 00980000
  99. 009900 PERFORM 9999-ERROR-DISPLAY THRU 00990000
  100. 010000 9999-EXIT 01000000
  101. 010100 VARYING ERROR-INDEX 01010000
  102. 010200 FROM 1 BY 1 01020000
  103. 010300 UNTIL ERROR-INDEX GREATER THAN 12. 01030000
  104. 010400 01040000
  105. 010500 GOBACK. 01050000
  106. 010600 01060000
  107. 010700 9000-EXIT. 01070000
  108. 010800 EXIT. 01080000
  109. 010900/ 01090000
  110. 011000*-----------------------------------------------------------------01100016
  111. 011100* 9999-ERROR-DISPLAY 01110000
  112. 011200*-----------------------------------------------------------------01120016
  113. 011300 9999-ERROR-DISPLAY. 01130000
  114. 011400 DISPLAY ERROR-TEXT (ERROR-INDEX). 01140000
  115. 011500 9999-EXIT. 01150000
  116. 011600 EXIT. 01160000
在上面需要注意的地方有:
该section类似C语言中的变量声明
  1. 005100 01 PEMPNO PIC X(6). 00510012
  2. 005200 01 POLDSALARY PIC S9(7)V9(2) COMP-3. 00520012
  3. 005300 01 PNEWSALARY PIC S9(7)V9(2) COMP-3. 00530016

PROCEDURE DIVISION USING PEMPNO, POLDSALARY, PNEWSALARY.
类似C语言中的函数定义,需要注意的这里的声明,定义必须同上面
procedure的定义一致,你懂得

STEP3:
写完cobol之后,你需要做的就是编译程序,bind package
cobol程序编译完成后,不需要bind到plan,直接到package即可,原因不详(偶不是太懂哈)
编译的作业模板如下:

点击(此处)折叠或打开

  1. //PAOLOR6S JOB (999,POK),'COBOL C/L/B/E',CLASS=A,MSGCLASS=T, 00000001
  2. // NOTIFY=PAOLOR6,TIME=1440,REGION=0M 00000002
  3. /*JOBPARM SYSAFF=SC63,L=9999 00000003
  4. // JCLLIB ORDER=(DB2V710G.PROCLIB) 00000004
  5. //* 00250000
  6. //JOBLIB DD DSN=DB2V710G.SDSNEXIT,DISP=SHR 00260000
  7. // DD DSN=DB2G7.SDSNLOAD,DISP=SHR 00270000
  8. // DD DSN=CEE.SCEERUN,DISP=SHR 00270001
  9. //*---------------------------------------------------------- 00900000
  10. //* STEP 01: PRECOMP, COMP, LKED A COBOL PROGRAM 01080000
  11. //*---------------------------------------------------------- 00900000
  12. //COBOL01 EXEC DSNHICOB,MEM=EMPAUDTS, 01090000
  13. // COND=(4,LT), 01100000
  14. // PARM.PC=('HOST(IBMCOB)',APOST,APOSTSQL,SOURCE, 01110000
  15. // NOXREF,'SQL(DB2)','DEC(31)'), 01120000
  16. // PARM.COB=(NOSEQUENCE,QUOTE,RENT,'PGMNAME(LONGUPPER)') 01130000
  17. //PC.DBRMLIB DD DSN=SG247083.DEVL.DBRM(EMPAUDTS), 01150000
  18. // DISP=SHR 01160000
  19. //PC.SYSLIB DD DSN=SG247083.PROD.SOURCE, 01170000
  20. // DISP=SHR 01180000
  21. //PC.SYSIN DD DSN=SG247083.PROD.SOURCE(EMPAUDTS), 01190000
  22. // DISP=SHR 01200000
  23. //LKED.SYSLMOD DD DSN=SG247083.DEVL.LOAD(EMPAUDTS), 01210000
  24. // DISP=SHR 01220000
  25. //LKED.RUNLIB DD DSN=DB2V710G.RUNLIB.LOAD, 01230000
  26. // DISP=SHR 01240000
  27. //LKED.SYSIN DD * 01250000
  28.      INCLUDE SYSLIB(DSNRLI) 01260000
  29. //*---------------------------------------------------------- 00900000
  30. //* STEP 02: BIND THE PROGRAM 01290000
  31. //*---------------------------------------------------------- 01280000
  32. //COBOL02 EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT) 01300000
  33. //DBRMLIB DD DSN=SG247083.DEVL.DBRM,DISP=SHR 01310000
  34. //SYSTSPRT DD SYSOUT=* 01320000
  35. //SYSPRINT DD SYSOUT=* 01330000
  36. //SYSUDUMP DD SYSOUT=* 01340000
  37. //SYSOUT DD SYSOUT=* 01350000
  38. //REPORT DD SYSOUT=* 01360000
  39. //SYSIN DD * 01370000
  40. //SYSTSIN DD * 01390000
  41.  DSN SYSTEM(DB2G) 01400000
  42.  BIND PACKAGE(DEVL7083) MEMBER(EMPAUDTS) VALIDATE(BIND) - 01410000
  43.  OWNER(DEVL7083)
  44.  END 01460000
  45. //* 01550000
LAST:
最后你需要做的就是调用的你的procedure程序了,不在赘述。
ref:
ibm red books
阅读(2740) | 评论(0) | 转发(0) |
0

上一篇:PROCEDURE FOR Z/OS

下一篇:CICS dev摘要

给主人留下些什么吧!~~