Reading COBOL indexed file in SQL procedure in iSeries

40 pts.
Tags:
COBOL
iSeries
SQL procedure
How do I read a COBOL indexed file in SQL procedure in iSeries?


Software/Hardware used:
iSeries
0

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Discuss This Question: 7  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • TheRealRaven
    Are you having a problem with it? Show us your code and explain which part is giving you trouble. If there is an error, include any message IDs, SQLSTATEs or SQLCODEs.
    35,970 pointsBadges:
    report
  • ToddN2000
    What type of data connection are you using? Post you code like rave mentioned and we can help you out.
    134,495 pointsBadges:
    report
  • Bhusha
    A flat file of x(132) has been created from the cobol file.
    The file has the following structure,
    01   CUST-HDR.                                                    
         05 CUST-N-HEADER          PIC X(6) VALUE 'CUSHDR'.     
         05  CUST-N-RECOVERER-CODE  PIC X(4) VALUE 'RCVR'.      
         05  CUST-N-COUNT           PIC S9(7) VALUE  0.         
         05  CUST-N-FILLER          PIC X(3) VALUE 'ABC'.       
         05  CUST-N-TOT-BAL       PIC S9(7)V99.        
         05   CUST-N-BANK-ID         PIC X(4) VALUE 'XXXX'.     
         05   CUST-N-TOT-PRIN-BAL PIC S9(8)V99.
         05  CUST-N-TOT-INT-BAL    PIC S9(8)V99.
         05  CUST-N-TOT-COST-BAL  PIC S9(8)V99.
         05 fille pic x(80).

    1. I am trying to retrive CUST-N-COUNT      data to variable in sql procedure.


     select   substr(pl132rec,21,9),                                  
               left(hex(substr(pl132rec,29,1)),1),                                  
            dec(concat(substr(pl132rec,21,8),                                
                  right(hex(substr(pl132rec,29,1)),1))/1  ,9,0) *            
                 case when  left(hex(substr(pl132rec,29,1)),1)='D'                
                                                                      
                 then -1 else +1 end          as Category,                       
      
             dec(concat(substr(pl132rec,21,8),                              
             right(hex(substr(pl132rec,29,1)),100))/100  ,9,2) *      
            case when  left(hex(substr(pl132rec,29,1)),1)='D'              
                                                                      
                   then -1 else +1 end          as decimal            
     from pf132p                                                         
     
    SUBSTR     LEFT                    CATEGORY                      DECIMAL
    23456780}   D                   234,567,800-                2,345,678.00-
    23456781J   D                   234,567,811-                2,345,678.00-
    23456782K   D                   234,567,822-                2,345,678.00-
    23456783L   D                   234,567,833-                2,345,678.00-
    23456784M   D                   234,567,844-                2,345,678.00-
    23456785N   D                   234,567,855-                2,345,678.00-
    23456786O   D                   234,567,866-                2,345,678.00-
    23456787P   D                   234,567,877-                2,345,678.00-
    23456783L   D                   234,567,833-                2,345,678.00-
    23456788Q   D                   234,567,888-                2,345,678.00-
    23456789R   D                   234,567,899-                2,345,678.00-
    23456789R   D                   234,567,899-                2,345,678.00-
    The data category  has two digit decimal point (9(7)V99.
    but I am not abl to get the data as 2345678.00 as dividing the result by 100
    is removing the values as shown in decimal

    Can you please suggest.

    40 pointsBadges:
    report
  • TheRealRaven
    Why would you ever write such data to a "flat" file? It'd be far easier to create a table with those column definitions. You could always access a table as a "flat" file if there was ever some rare need to do so. Why do it the hard, very inefficient way? (And it isn't "indexed".)

    Then, why use SQL to process the file? SQL is intended not to use over "flat" files. You'd be better off using COBOL than SQL, even if the COBOL would also do better over a table.

    concat(substr(pl132rec,21,8),
     right(hex(substr(pl132rec,29,1)),100))

    Why are you apparently taking the right-most 100 characters of a 2 character hex value?
    35,970 pointsBadges:
    report
  • Bhusha
    sorry,The  line should should have been
    concat(substr(pl132rec,21,8),
     right(hex(substr(pl132rec,29,1)),1))
    Complete code
    select substr(pl132rec,21,9),
    left(hex(substr(pl132rec,29,1)),1),
    dec(concat(substr(pl132rec,21,8),
    right(hex(substr(pl132rec,29,1)),1))/1 ,9,0) *
    case when left(hex(substr(pl132rec,29,1)),1)='D'

    then -1 else +1 end as Category,

    dec(concat(substr(pl132rec,21,8),
    right(hex(substr(pl132rec,29,1)),1))/100 ,9,2) *
    case when left(hex(substr(pl132rec,29,1)),1)='D'
    then -1 else +1 end as decimal
    from pf132p
    The clint wants to move to SQL procedure from COBOL
    while existing programs should run, the new developed
    code should also use the same file.
    There are indexed file which need to be accessed in
    sql procedure to get the same functionality of COBOL
    Program.

    40 pointsBadges:
    report
  • TheRealRaven
    There are indexed file which need to be accessed in sql procedure...

    That increases the confusion. If there is a different indexed file, why would you process it with SQL the same way as the "flat" file?

    Well, it doesn't matter. It makes everything much more complicated than it should be; but it doesn't quite cover your problem. If the file was created properly and processed by COBOL properly, the problem wouldn't exist. But because of how you're making it work, you have to change your match slightly.

    The way you're dividing, you're making SQL do integer math. Try dividing by {100.00} instead of dividing by {100}.

    I can't test it, but that might be enough. Post results back here.
    35,970 pointsBadges:
    report
  • Bhusha
    The result is showing (++++++)
    23456780}   D                   234,567,800-  +++++++++++++++++++++++++++
    23456781J   D                   234,567,811-  +++++++++++++++++++++++++++
    40 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: