IBM Support

PM51492: Retrieve SQL with synonym or Alias referenced tables does not return keyitems

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • Environment: Windows
    Product name: IBM? Rational? Business Developer
    
    Program Version    :  8.0.1.2
    Description.
    An Retrieve SQL operation in EGL source produces returned
    columns which do not include the KeyItems (primary keys) when
    the table is accessed via
    
    either a Synonym or an Alias from DB2 datasources.
    
    For example a table is created with the following SQL which
    assigns each column as a primary key and creates the associated
    Synonym:
    CREATE TABLE 'MHALL'.'BA_AP_APLC_DATES_T'
    ('DATE_ACC_PR' DATE NOT NULL,
    'DATE_ACC_CR' DATE NOT NULL,
    'DATE_ACC_NX' DATE NOT NULL)
    AUDIT NONE
    DATA CAPTURE NONE
    CCSID EBCDIC;
    
    ALTER TABLE 'MHALL'.'BA_AP_APLC_DATES_T' ADD CONSTRAINT
    'DATE_ACC_PR' PRIMARY KEY('DATE_ACC_PR',
    'DATE_ACC_CR', 'DATE_ACC_NX');
    CREATE SYNONYM 'MYSYN' FOR 'MHALL'.'BA_AP_APLC_DATES_T';
    
    The table definition is then retrieved referenced with the
    synonym as:
    record NotNullSampleRecord type sqlRecord
    {tablenames=[['MYSYN']] , fieldsMatchColumns = yes}
    
     10 DATE_ACC_PR char(10) {column='DATE_ACC_PR'};
     10 DATE_ACC_CR char(10) {column='DATE_ACC_CR'};
     10 DATE_ACC_NX char(10) {column='DATE_ACC_NX'};
    end
    
    If the table is referenced directly with its absolute name then
    we see the primary key correctly returned as a keyitems as below
    ie:
    record NotNullSampleRecord type sqlRecord
    {tablenames=[['BA_AP_APLC_DATES_T']], fieldsMatchColumns = yes,
    keyItems=[DATE_ACC_PR, DATE_ACC_CR, DATE_ACC_NX]}
    
     10 DATE_ACC_PR char(10) {column='DATE_ACC_PR'};
     10 DATE_ACC_CR char(10) {column='DATE_ACC_CR'};
     10 DATE_ACC_NX char(10) {column='DATE_ACC_NX'};
    end
    

Local fix

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    SQL records populated using the Retrieve SQL feature will
    not have any primary keys (the keyItems property won't be
    set) if the database table is a synonym or alias table.
    

Problem conclusion

  • The problem has been fixed.
    

Temporary fix

Comments

APAR Information

  • APAR number

    PM51492

  • Reported component name

    RATL BUS DEV

  • Reported component ID

    5724S5000

  • Reported release

    801

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2011-11-03

  • Closed date

    2012-10-08

  • Last modified date

    2012-10-08

  • APAR is sysrouted FROM one or more of the following:

  • APAR is sysrouted TO one or more of the following:

Fix information

  • Fixed component name

    RATL BUS DEV

  • Fixed component ID

    5724S5000

Applicable component levels

  • R801 PSN

       UP

[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSMQ79","label":"Rational Business Developer"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"8.0.1","Edition":"","Line of Business":{"code":"LOB35","label":"Mainframe SW"}}]

Document Information

Modified date:
08 October 2012