Cobol copybook to SQL conversion

Post questions here relative to DataStage Server Edition for such areas as Server job design, DS Basic, Routines, Job Sequences, etc.

Moderators: chulett, rschirm, roy

Post Reply
denzilsyb
Participant
Posts: 186
Joined: Mon Sep 22, 2003 7:38 am
Location: South Africa
Contact:
Contact denzilsyb

Cobol copybook to SQL conversion

Post by denzilsyb »

Hi guys

I am trying to convert cobol copybooks (or cfd's) into a SQL statement - to create a 'table' of the copybook. i.e. When you import a .cfd into datastage manager there is the "columns" layout of the .cfd file. this view is what I eventually want to create.

I have tried to export the table definition as xml and then read the xml document into a designer tool, but the definition is not correct. Any ideas? Is this perhaps a metastage solution?

here is the .cfd file you can import into manager and test if you want (save as FILE.cfd and import):

Code: Select all

        

        01  PMMACCWS.
            03  CURRSTAF-ACC.
              04  M-ACC-FIXED.
                05  M-PMMACCNT-KEY.
                  07  M-ACC-ID.
                    09  M-ACC-ID2.
                        11  M-BCH-NO        PIC S9(5)           COMP-3.
                        11  M-ACC-TYPE      PIC S999            COMP-3.
                        11  M-ACC-NO        PIC S9(9)           COMP-3.
                    09  M-GD-SERIAL         PIC S999            COMP-3.
                  07  M-REC-NO              PIC S999            COMP-3.
                05  M-ACC-MISC.
                    07  M-ACC-MISC1         PIC X.
                    07  M-ACC-MISC2         PIC X.
                    07  M-ACC-MISC3         PIC X.
                05  M-LST-ACT               PIC S9(7)           COMP-3.
                05  M-PREV-ACT              PIC S9(7)           COMP-3.
                05  M-NAME                  PIC X(20).
                05  M-ACC-STYLE             PIC XXX.
                05  M-ACC-STATUS            PIC S9(8)           COMP.
                05  M-ACC-STAT REDEFINES M-ACC-STATUS.
                    07  CSA-ACC-ST1         PIC X.
                    07  CSA-ACC-ST2         PIC X.
                    07  CSA-ACC-ST3         PIC X.
                    07  CSA-ACC-ST4         PIC X.
                05  M-BOOK-BAL              PIC S9(13)V99       COMP-3.
                05  M-RETRO-BAL             PIC S9(13)V99       COMP-3.
                05  M-CLEAR-BAL             PIC S9(13)V99       COMP-3.
                05  M-MEMO-BOOK-BAL         PIC S9(13)V99       COMP-3.
                05  M-LATEST-BAL REDEFINES M-MEMO-BOOK-BAL
                                            PIC S9(13)V99       COMP-3.
                05  M-MEMO-RETRO-BAL        PIC S9(13)V99       COMP-3.
                05  M-MEMO-CLEAR-BAL        PIC S9(13)V99       COMP-3.
                05  M-DATE-LST-DEP          PIC S9(7)           COMP-3.
                05  M-RB-CODE               PIC S9(5)           COMP-3.
                05  M-LANG-IND              PIC X.
                05  M-STOP-M-CNT            PIC S9(4)           COMP.
                05  CSA-CORP-IND            PIC S9              COMP-3.
                05  CSA-REDUCT-IND          PIC S9              COMP-3.
                05  M-STAT-M-CNT            PIC S9(4)           COMP.
                05  M-OV-CNT                PIC S999            COMP-3.
                05  M-STAT-ITEM-CNT         PIC S9(5)           COMP-3.
                05  M-STAT-BAL              PIC S9(13)V99       COMP-3.
                05  M-DTE-LST-STAT          PIC S9(5)           COMP-3.
                05  M-STAT-NO               PIC S999            COMP-3.
                05  M-STAT-DESP             PIC X.
                05  M-STAT-COPY             PIC 9.
                05  M-CASH-WITHD            PIC S999            COMP-3.
                05  M-DORM-IND              PIC X.
                05  CSA-OVERDRAFT-LIMIT     PIC S9(15)          COMP-3.
                05  CSA-RISK-GRADE          PIC XX.
                05  CSA-IMP-TX-CODE         PIC X.
                05  CSA-CNT-CR-ENTS         PIC S9(7)           COMP-3.
                05  CSA-CNT-DR-ENTS         PIC S9(7)           COMP-3.
                05  CSA-CR-DECIMALS         PIC S9(15)          COMP-3.
                05  CSA-CR-INTDET           REDEFINES CSA-CR-DECIMALS
                                            PIC S9(11)V9(4)     COMP-3.
          05  NEW-FIELDS-CA           REDEFINES CSA-CR-INTDET.     
              07  FILLER              PIC XX.                      
              07  CSA-BUSNS-SCHEME-NO PIC S9(5)            COMP-3. 
              07  CSA-OLD-STYLE       PIC XXX.                     
                05  CSA-ASS-LIAB            PIC X(3).
                05  FILLER                  REDEFINES CSA-ASS-LIAB.      
                    10  CSA-BEHV-ICI        PIC X(1).                    
                    10  CSA-BEHV-SLC        PIC X(2).                    
                05  M-PAY-CHEQ-IND          PIC X.
                05  CSA-OD-CNT              PIC S9(4)           COMP.
                05  M-E-CNT                 PIC S9(4)           COMP.
                05  CSA-GRP-ACC-NO          PIC S9(7)           COMP-3.
                05  M-DORM-DATE             PIC S9(7)           COMP-3.
                05  CSA-CR-INT-RATE         PIC S99V999         COMP-3.
                05  CSA-LAST-CR-INT         PIC S9(7)           COMP-3.
                05  CSA-GRP-LIM-TYP         PIC S9(15)          COMP-3.
                05  M-AVAIL-BAL             PIC S9(13)V99       COMP-3.
                05  CSA-PHASED-IND          PIC X.
                05  CSA-EMPLOYEE-NO         PIC X(12).
                05  CSA-PAY-POINT           PIC S9(7)           COMP-3.
                05  CSA-COMPANY-NO          PIC XX.
                05  CSA-BEHVR-OVERRIDE      PIC X.
                05  M-RETRO-OVERFLOW        PIC X.
                05  CSA-TOT-DAYS-OD         PIC S9(3)           COMP-3.
                05  CSA-NO-TIMES-OD         PIC S9(3)           COMP-3.
                05  CSA-OD-IND              PIC XX.
                05  CSA-MARKET-SEG          PIC S999            COMP-3.
                05  CSA-AE-NO               PIC S999            COMP-3.
                05  M-CSV-VOUCHERS-PRESENT  PIC X.
                05  CSA-IT3B-ACCUM          PIC S9(13)V99       COMP-3.
                05  CSA-DUTY REDEFINES CSA-IT3B-ACCUM.
                   07  CSA-DUTY-PAYABLE     PIC S9(15)          COMP-3.
                05  SB-INTPD                REDEFINES CSA-IT3B-ACCUM.    
                   07  SB-INTPD-THIS-TAX-YR PIC S9(13)V99       COMP-3.  
                05  CRD-LAST-CR-INT         PIC S9(5)           COMP-3.
                05  M-AGCOM-IND             PIC X.
                05  CSA-AGENCY              PIC S9(3)           COMP-3.
                05  M-OLD-CYCLIC-A.
                    07  M-OLD-CYCLIC        PIC S9(9)           COMP-3.
                05  FILLER                  PIC X(4).                    
                05  M-COMM-CHARGE-IND       PIC X.                       
                05  M-DEST-IBT              PIC S9(5)           COMP-3.
                05  M-ENT-SEQ-NO            PIC S9(5)           COMP-3.
                05  CSA-PFOLIO-IND          PIC 99.
                05  CSA-OVER-LIM-CNT        PIC S9(4)           COMP.
                05  M-HANDLING-IND          PIC X.
                05  M-RETAIN-IND            PIC X.
                05  M-DT-LST-FICHE-STAT     PIC S9(5)           COMP-3.
                05  M-FICHE-NO              PIC S999            COMP-3.
                05  M-OD-INSURANCE          PIC X.
                05  M-STOPPED-REASON        PIC XX.
                05  M-MTFR-ADVICE-IND       PIC X.
              04  CSA-OD-REC.
                05  OD-ACC-ID.
                    07  OD-BR-NO                PIC S9(5)       COMP-3.
                    07  OD-ACC-TYPE             PIC S999        COMP-3.
                    07  OD-ACC-TO-BE-DEBITED    PIC S9(9)       COMP-3.
                05  OD-COM-PENFEE-IND           PIC 9.
                05  OD-COM-FEE-DEC              PIC S9(15)      COMP-3.
                05  OD-EXPIRY-DATE              PIC S9(7)       COMP-3.
                05  OD-1ST-RATE                 PIC S99V999     COMP-3.
                05  OD-1ST-LIMIT                PIC S9(13)      COMP-3.
                05  OD-1ST-DECIMALS             PIC S9(15)      COMP-3.
                05  OD-2ND-RATE                 PIC S99V999     COMP-3.
                05  OD-2ND-LIMIT                PIC S9(13)      COMP-3.
                05  OD-2ND-DECIMALS             PIC S9(15)      COMP-3.
                05  OD-3RD-RATE                 PIC S99V999     COMP-3.
                05  OD-3RD-DECIMALS             PIC S9(15)      COMP-3.
                05  OD-NEW-RATE1                PIC S99V999     COMP-3.
                05  OD-NEW-RATE2                PIC S99V999     COMP-3.
                05  OD-NEW-RATE3                PIC S99V999     COMP-3.
                05  OD-COM-CALC                 PIC X.
            03  PPSB-ACC REDEFINES CURRSTAF-ACC.
                05  FILLER                  PIC X(137).
                05  SB-INT-RATE-IND         PIC X.
                05  SB-INTEREST-BALANCE     PIC S9(11)V9(4)      COMP-3.
                05  SB-DT-LAST-INT-CALC     PIC S9(5)            COMP-3.
                05  SB-WITHDRAWAL-IND       PIC 9.
                05  SB-MIN-MONTH-BAL        PIC S9(13)V99        COMP-3.
                05  SB-CORP-INT-BAL         PIC S9(11)V9(4)      COMP-3.
          05  NEW-FIELDS-SB           REDEFINES SB-CORP-INT-BAL.   
              07  SB-BUSNS-SCHEME-NO  PIC S9(5)            COMP-3. 
              07  SB-OLD-STYLE        PIC XXX.                     
              07  FILLER              PIC XX.                      
                05  FILLER                  PIC X(2).
                05  SB-DUMMY-CNT            PIC S9(4)            COMP.
                05  FILLER                  PIC X(17).
                05  SB-CDF.
                    07  SB-CDF-CHG-TBL      PIC S9(04)           COMP.
                    07  SB-CDF-CHG-TYP      PIC X(01).
                    07  SB-CDF-DEP-CNT      PIC S9(03)           COMP-3.
                05  FILLER                  PIC X(03).
                05  SB-AVAIL-BAL            PIC S9(13)V99        COMP-3.
                05  SB-CHG-ENT-CNT          PIC S9(3)            COMP-3.
                05  SB-CHG-RECOVD-AMT       PIC S9(5)V99         COMP-3.
                05  SB-CHG-ACC-ID.
                    07  SB-CHG-BR-NO        PIC S9(5)       COMP-3.
                    07  SB-CHG-ACC-TYPE     PIC S999        COMP-3.
                    07  SB-CHG-ACC-TO-BE-DB PIC S9(9)       COMP-3.
                05  SB-DUTY-PAYABLE         PIC S9(15)           COMP-3.
                05  SB-DEB-ORDER-IND        PIC X.
                05  FILLER                  PIC X(18).
                05  SB-AGCOM-IND            PIC X.
                05  SB-AGENCY               PIC S9(3)            COMP-3.
                05  SB-STOPPED-REASON.
                  07  SB-STOPPED-REASON-N   PIC 9(2).
                05  SB-SCHEME-NUMBER        PIC 9(5).
                05  FILLER                  PIC X(2).
                05  SB-COMM-CHARGE-IND      PIC X.

                05  SB-EXTENSION1           PIC X(21).
                05  SB-EXTENSION-XBS    REDEFINES SB-EXTENSION1.
                    10  XBS-COMPETITION-Y       PIC X.
                    10  XBS-ALLOW-ACB-DR-Y      PIC X.
                    10  XBS-FIXED-SAVINGS-A     PIC S9(13)V99  COMP-3.
                    10  FILLER                  PIC X(7).
                    10  XBS-DT-LST-INT-CALC-ABS PIC S9(7)      COMP-3.
                05  SB-EXTENSION-UNREC  REDEFINES SB-EXTENSION1.
                    10  SB-UNREC-A              PIC S9(7)V99   COMP-3.
                    10  SB-LAST-INTPD-DATE      PIC S9(7)      COMP-3.
                    10  SB-INTPD-PREV-TAX-YR    PIC S9(13)V99  COMP-3.
                    10  SB-BIRTH-D              PIC S9(7)      COMP-3.
            03  GENDEP-ACC REDEFINES PPSB-ACC.
                05  FILLER                  PIC X(68).
                05  GEN-AT-MIN-BAL          PIC S9(13)V99        COMP-3.
                05  FILLER                  PIC X(16).
                05  GEN-AT-MEMO-MIN-BAL     PIC S9(13)V99        COMP-3.
                05  FILLER                  PIC X(8).
                05  GEN-EXP-DATE            PIC S9(7)            COMP-3.
                05  FILLER                  PIC X(22).
                05  GEN-INT-CYCLE           PIC X.
                05  GEN-OTHER-ACC-ID.
                    07  GEN-OTR-BR-NO       PIC S9(5)            COMP-3.
                    07  GEN-OTR-ACC-TYPE    PIC S999             COMP-3.
                    07  GEN-OTR-ACC-NO      PIC S9(9)            COMP-3.
                05  GEN-INT-RATE-IND        PIC X.
                05  GEN-SPEC-INT-RATE       PIC S99V9(3)         COMP-3.
                05  GEN-INTEREST-BALANCE    PIC S9(11)V9(4)      COMP-3.
                05  GEN-DL-INT              PIC S9(5)            COMP-3.
                05  GEN-NEW-RATE            PIC S99V999          COMP-3.
                05  FILLER                  PIC X(5).
                05  GEN-DUMMY-CNT           PIC S9(4)            COMP.
                05  FILLER                  PIC X(110).
            03  GENLEDG-ACC REDEFINES GENDEP-ACC.
                05  FILLER                  PIC X(52).
                05  GL-AS-AT-BALANCE        PIC S9(13)V99        COMP-3.
                05  GL-AS-AT-BALANCE-A      PIC S9(13)V99        COMP-3.
                05  GL-VALUE-CR-TX          PIC S9(13)V99        COMP-3.
                05  GL-VALUE-DR-TX          PIC S9(13)V99        COMP-3.
                05  GL-COUNT-CR-TX          PIC S9(15)           COMP-3.
                05  GL-COUNT-DR-TX          PIC S9(15)           COMP-3.
                05  FILLER                  PIC X(68).
                05  GL-DUMMY-CNT            PIC S9(4)            COMP.
                05  GL-DUMMY-OD             PIC S9(4)            COMP.
                05  GL-TAKEOVER-BAL         PIC S9(13)V99        COMP-3.
                05  FILLER                  PIC X(100).
            03  REVEX-ACC REDEFINES GENLEDG-ACC.
                05  FILLER                  PIC X(172).
                05  RX-VAT-R-EXEMPT         PIC S9(15)V99       COMP-3.
                05  RX-VAT-R-I-STD-RATED    PIC S9(15)V99       COMP-3.
                05  RX-VAT-R-O-STD-RATED    PIC S9(15)V99       COMP-3.
                05  RX-VAT-R-ZERO-RATED     PIC S9(15)V99       COMP-3.
                05  RX-VAT-R-TAX-AMOUNT     PIC S9(9)V99        COMP-3.
                05  RX-VAT-R-NON-VENDOR     PIC S9(15)V99       COMP-3.
                05  RX-VAT-C-EXEMPT         PIC S9(15)V99       COMP-3.
                05  RX-VAT-C-I-STD-RATED    PIC S9(15)V99       COMP-3.
                05  RX-VAT-C-O-STD-RATED    PIC S9(15)V99       COMP-3.
                05  RX-VAT-C-ZERO-RATED     PIC S9(15)V99       COMP-3.
                05  RX-VAT-C-TAX-AMOUNT     PIC S9(9)V99        COMP-3.
                05  RX-VAT-C-NON-VENDOR     PIC S9(15)V99       COMP-3.
                05  RX-REVEX-IND            PIC X.
                05  RX-F50-CODE             PIC S9(3)           COMP-3.
                05  RX-VAT-CODE             PIC 9.
                05  FILLER                  PIC X(2).
            03  HOUSE-ACC REDEFINES REVEX-ACC.
                05  FILLER                  PIC X(134).
                05  HA-DAYS-OUTST           PIC S999             COMP-3.
                05  FILLER                  PIC X(30).
                05  HA-MARKUP-COUNT         PIC S9(4)            COMP.
                05  HA-DUMMY-CNT            PIC S9(4)            COMP.
                05  FILLER                  PIC X(110).
            03  M-E-REC-ALL.
              04 M-E-REC OCCURS 167 TIMES.
                05  M-E-TRANS-CODE          PIC S9(5)            COMP-3.
                05  FILLER                  PIC X(22).
_
Let me know if you have any ideas. I have written a bit of BASIC to do pretty much want I want (the 'parent' levels have 0 lengths, ideally they should have the totals as displayed in the 'columns' tab in manager), but looking back I must admit, there must be an easier way.
dnzl
"what the thinker thinks, the prover proves" - Robert Anton Wilson
Top
roy
Participant
Posts: 2598
Joined: Wed Jul 30, 2003 2:05 am
Location: Israel

Post by roy »

Hi,
Import the copy book file definition.
build a server job with CFF stage and use the table definition you imported.

link to a DB stage and select which columns you want as output, then generate DDL to get it.

Bare in mind that most times your target table is not the same as your source file.

IHTH,
Roy R.
Time is money but when you don't have money time is all you can afford.

Search before posting:)

Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
Image
Top
denzilsyb
Participant
Posts: 186
Joined: Mon Sep 22, 2003 7:38 am
Location: South Africa
Contact:
Contact denzilsyb

Post by denzilsyb »

thanks Roy, I never thought of that. :!:

But, as usual - there is more to the picture (I will be a good end-user!).

I will need to automate this process somehow, I'm expecting thousands of files, and I'm not getting data to play with, only the *.cfd - so reading just the cfd in the CFF stage I dont believe will give satisfactory results; probably a couple of warnings in the job.
Top
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:
Contact ray.wurlod

Post by ray.wurlod »

If you import the Table Definition from the COBOL FD, it will be stored in and displayable from the Repository as if it were SQL data types.

There is a post way back of a function to dump table definitions in various forms, one of which is standard CREATE TABLE statements. Search for it; get back if you can't find it. It's on this Forum, and contains the word INTEGRITY.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Top
kduke
Charter Member
Charter Member
Posts: 5227
Joined: Thu May 29, 2003 9:47 am
Location: Dallas, TX
Contact:
Contact kduke

Post by kduke »

Ray should know. It is called KgdCreateDdlMetadata. It is based on a routine Ray posted.

Here is how to use it. viewtopic.php?t=91578&highlight=KgdCreateDdlMetadata
Mamu Kim
Top
Post Reply

Return to “IBM<sup>®</sup> Infosphere DataStage Server Edition”