Why do I get SQLCODE=100 when doing a join of two RDF tables under CA OPS/MVS even if there are rows that match the where condition?

Document ID : KB000027362
Last Modified Date : 14/02/2018
Show Technical Document Details

Introduction: 

This is the SQL SELECT statement to join two RDF tables:

 "Select TDSL_PRODUCTS.PRODUCT,  ",

  "       TDSL_PRODUCTS.DESCR,  ",

  "       TDSL_DATASETS.DSN,  ",

  "  from TDSL_PRODUCTS, TDSL_DATASETS " ,

  "  where TDSL_PRODUCTS.PRODUCT=TDSL_DATASETS.PRODUCT"

When is run it produces the following informational message:

  OPS0996I rc=0 sqlcode=100 product.0=PRODUCT.0

These are the display of the RDF tables structure:

  Table Structure Editor ------ SY90>TDSL_PRODUCTS ---- COLUMNS 00001 00072

  Command ===>                                                  Scroll ===> CSR

  COL--> COLUMN-NAME        DATA-TYPE      ATTRIBUTES  DEFAULT

  ****** ***************************** TOP OF DATA *****************************

  000001 PRODUCT            CHAR(8)        PK

  000002 SUPPLIER           CHAR(8)

  000003 DESCR              VARCHAR(255)

  000004 SHOWSRCV           CHAR(1)                    N

  000005 USERID             CHAR(8)

  000006 MODDATE            INTEGER                    0

  000007 RENUNC             CHAR(18)       NN

  Table Structure Editor ------ SY90>TDSL_DATASETS ---- COLUMNS 00001 00072

  Command ===>                                                  Scroll ===> CSR

  COL--> COLUMN-NAME        DATA-TYPE      ATTRIBUTES  DEFAULT

  ****** ***************************** TOP OF DATA *****************************

  000001 PRODUCT              CHAR(8)        PK

  000002 DSN                  CHAR(44)       PK

  000003 DSTYPE               CHAR(8)

  000004 SRCVOL               CHAR(6)

  000005 DSTVOL               CHAR(6)

  000006 APF                  CHAR(1)

  000007 LNK                  CHAR(2)

Question: 

Why do I get SQLCODE=100 when doing a join of two RDF tables under CA OPS/MVS even if there are rows that match the where condition?

Answer: 

The issue here is that that TDSL_PRODUCTS has one key field, and TDSL_DATASETS has 2 key fields, and when performing a join, we pick the "dependent" table based upon the number of rows (the one with > rows ends up being the primary table).

One restriction in our join process is the length of the total key field for the primary table. The key length is used in the compares causing it not to work when the multi keyed table ends up being the primary table in the join.

If you add dummy rows to TDSL_PRODUCTS to assure it always has more rows than TDSL_DATASETS, the join should work. You can use this as a workaround for the moment.

Additional Information:

CA OPS/MVS - Using the Relational Data Framework