IC SunsetThe developerWorks Connections platform will be sunset on December 31, 2019. On January 1, 2020, this forum will no longer be available. More details available on our FAQ.
Topic
  • 3 replies
  • Latest Post - ‏2018-12-14T14:45:09Z by iJohannes
iJohannes
iJohannes
4 Posts

Pinned topic DB modernization with RCAC and COBOL

‏2018-08-31T13:24:19Z | cobol db2 ddl rcac sql

Hi,

 

I am in the process of modernizing the database of a customer from DDS described files to DDL.

This means I convert the physical files to tables and logicals to indices using generate_sql, in a development library.

The customer has a mix of (ILE) COBOL and RPG programs, mostly COBOL. Some use SQL, some use native IO.

So far everything seems to work fine: the old programs accept the new tables and indices and work as before.

The customer wants a way to hide sensitive data from it's users, so I'm implementing RCAC (Row and Column Access Control) as well.

Unfortunately this is where I've hit a small problem: one of the files causes a COBOL program to crash when both row- or column-access control is active and when the logical files have been converted to indices.

So RCAC works on the DDL table with logicals, but not with the DDL table with indices.

The error I get is LNR7207, with file status '95' - which indicates "Invalid or incomplete file information (1) Duplicate keys specified in COBOL program. The file has been successfully opened, but indexed database file created with unique key; or (2) Duplicate keys not specified in COBOL program, and indexed database file created allowing duplicate keys." (source: https://www.ibm.com/support/knowledgecenter/SSAE4W_9.0.0/com.ibm.etools.iseries.langref.doc/c0925395700.htm)

The crash occurs on the OPEN I-O instruction of the file (actually on an index of that file), a regular OPEN instruction (input only) does not cause the crash.

According to the file status there might be duplicates in the file, but I have checked and found none. The logical/index is unique. I have eliminated all but one index on the file and the problem persists. The table itself does not have any keys.

Strangely, if I add the 'with duplicates' keyword in the file definition of the COBOL program, it works - RCAC and index and all - but this seems contradictory. This would also mean I'd have to recompile this programs, which is something I am trying to avoid.

I am unable to replicate the problem in RPG, everything works fine there.

 

Any help would be appreciated! Please let me know if you need any more information or code to see what's happening.

 

edit: the system is at V7R3

 

Updated on 2018-08-31T13:37:04Z at 2018-08-31T13:37:04Z by iJohannes
  • iJohannes
    iJohannes
    4 Posts
    ACCEPTED ANSWER

    Re: DB modernization with RCAC and COBOL

    ‏2018-12-14T14:45:09Z  

    I have contacted IBM and they will patch the issue.

  • robberendt
    robberendt
    84 Posts

    Re: DB modernization with RCAC and COBOL

    ‏2018-08-31T18:47:07Z  

    Might this be the possibility?  You have a column called SSN.  You are masking it with something like:

    CREATE MASK SSN_MASK ON rob.EMPLOYEE
       FOR COLUMN SSN RETURN
          CASE
             WHEN (current user = 'ROB')
                THEN SSN
             WHEN (current user = 'DUMMY1')
                THEN 'XXX-XX-' || SUBSTR(SSN,8,4)
             ELSE NULL
          END
       ENABLE;
     

    Now, if you have 123-45-6789, and 543-21-6789 they would both have the same mask 'XXX-XX-6789'.  Duplicate?  Maybe, maybe not.  According to the following it knows the difference.

    select ssn, count(*)
    from rob.employee   
    group by ssn        

     SSN            COUNT ( * )
     XXX-XX-6789              1
     XXX-XX-6789              1

     

    select ssn, count(*)
    from rob.employee  
    group by ssn       
    having count(*)>1  

    SSN            COUNT ( * )    
    ********  End of data  ********

     

     

  • iJohannes
    iJohannes
    4 Posts

    Re: DB modernization with RCAC and COBOL

    ‏2018-09-04T07:11:20Z  

    Hi, column masks are applied after db access, so it should not be considered a duplicate (as you yourself indicated :) ). Anyway the column mask is not being applied to the key column in this case. 

    Updated on 2018-09-04T14:04:45Z at 2018-09-04T14:04:45Z by iJohannes
  • iJohannes
    iJohannes
    4 Posts

    Re: DB modernization with RCAC and COBOL

    ‏2018-12-14T14:45:09Z  

    I have contacted IBM and they will patch the issue.