Qlik Replicate: IBM DB2 for iSeries source: How to replicate Multi-Member tables

[john_wang]

The AS/400 supports a file concept known as multiple-member files, in which one file (or table) can possess several different members. Each member is a part of the same file or table and shares the same schema, but the members are uniquely named and have unique data. Just like a partition table in other regular RDBMS in, for example, Oracle, or MySQL.

ODBC and OLE DB have no built-in mechanism for accessing multiple members. By default, ODBC always accesses the first member in a multimember file. This is the known limitation in Qlik Replicate User Guide: Multi-member (Partitioned) tables are not supported.

To enable ODBC-based applications such as Data Transformation Services (DTS) to access multiple-member files, we need to use the AS/400's SQL ALIAS statement. The ALIAS statement lets you create an alias for each member we need to access. We use ALIAS to access all members in the Qlik Replicate full load stage. More samples: Creating and using ALIAS names.

However, all member data changes will be recorded in the DB400 Journal file, Qlik Replicate can capture all members' changes. No special settings are needed in the Qlik Replicate CDC stage.

This article describes how to overcome the limitation and set up a Qlik Replicate task to replicate Multi-Member Tables.

Resolution

1. Create ALIAS for all members of the multi-member table (except the first member) in AS400 Terminal
   
   CREATE ALIAS apsupdb.MMPF_ALIAS2 FOR apsupdb.mmpf(MBR2)
   CREATE ALIAS apsupdb.MMPF_ALIAS3 FOR apsupdb.mmpf(MBR3)
   
   In the above sample, we create 2 alias in library apsupdb, corresponding to 2 members apsupdb.mmpf(MBR2) & apsupdb.mmpf(MBR3).
   
   
2. Create a new Full Load + CDC task with a few tables. The task contains the physical table (first member).
3. Press "Export Task" and edit the exported JSON file
4. Add the ALIAS to task JSON file as same as it's a regular table. 
5. Change all STRING data type columns from UTF8 to "ibm-285_P100-1995". In below sample we change
   
   STRING type column "ADDRESS":
   "source_column_data_types": [{
   "column_name": "ADDRESS",
   "converter_name": "ibm-285_P100-1995",
   "data_type": "kAR_DATA_TYPE_STR"
   }],
   
   This is because Qlik Replicate cannot get the ALIAS metadata correctly so all STRING data types are regarded as UTF8 which leads to garbage data after Full Load is done. Other data types (such as numbers, data, etc ) do not need not be modified.
   

   
   Note that the 285 value here is from the CCSID and not all tables have the same CCSID. The following query should be used to verify the CCSID and make changes accordingly.

   SELECT * FROM QSYS2.SYSCOLUMNS WHERE TABLE_NAME = 'MMPF';

6. Import the JSON and run the task, as same as a regular task.

Please note that these steps in the article will replicate all the Multimember tables are separate tables on the target database. If you would like all the partitions to be under a single table, change the Full Load task settings to 'Do Nothing' if the target table already exists to prevent full Loads from recreating them. The table should be created manually. After this change, each Alias added to the JSON will need a table-level transformation to transform the table name back to the original base table name, MMPF in the article's case. This would ensure all changes are replicated to just MMPF.

In the Qlik Replicate UI, all changes will be seen on MMPF and not on the other members as the Qlik Replicate task will always reference the base object.

This customization is provided as is. Qlik Support cannot provide continued support for the solution. Please reach out to Qlik Professional Service Team and AS400 Expert for additional assistance.

Environment

• Qlik Replicate All supported versions
• IBM DB2 for iSeries All supported versions

Internal Investigation ID(s)

Support Case: 00049024

[kutay_cilingiroglu]

Hi John,

Many thanks for the blog. 

Will  separate tables be created for each alias at the destination?
Can we give the same destination table name for Alias tables? 
Can we use different codepage like ibm-920_P100-1995 for String columns?

Best.

[Sonja_Bauernfeind]

Hello @kutay_cilingiroglu 

>> Will separate tables be created for each alias at the destination?

Yes, however, first, we need to merge all separate tables (corresponding to all the alias) into one single table (corresponding to the first member).

>> Can we give the same destination table name for Alias tables? 

See above, yes.

>> Can we use different codepage like ibm-920_P100-1995 for String columns?

Yes, any supported codepage can be used.

Should you have any further questions, please post them directly in our Qlik Replicate forum (though feel free to reply to this message letting me know you posted there).

All the best,
Sonja 

[ernesto_costa]

Hi John,

this post gives me some hope to overcome the issue I'm currently facing when extracting data from DB2.

Basically we are getting data from an iSeries DB2, where we have multi-member files, and sending the data to Kafka to topics per source table.

Few details: main file is EG3DTQ.VARSTMT1 and we created an alias called EG3DTQ.EG3DTQ_VARSTMT1_KC7.

I did the steps you mentioned and they allowed me to do add the alias to the task but then the following behavior is experienced.

When DML operations are made on the alias they are actually captured by Replicate as being made on the main file. Kafka events are also delivered to the topic of the main file instead of the topic related to the alias.

This would be fine if we could actually identify to which member the messages belong to, but I can't really find a way.

Another thing that may be relevant is the following message when the task is started:

Any idea on how to overcome these challenges, either by:

1. getting the events to be picked up on the alias and not on the main file

2. finding a way to identify to which member the change captured on the main file belongs to.

Let me know what you think.

[john_wang]

Hello @ernesto_costa ,

Thanks for the update.

The primary challenge is that Qlik Replicate only retrieves data from the first member of Multi-Member tables during the full load phase. To get data from all members, we need to use an ALIAS for initial data retrieval. This limitation is highlighted in the User Guide:

Multi-member (Partitioned) tables are not supported.

In IBM DB2 for iSeries, any changes are associated with the main file rather than individual members. Therefore, during the Change Processing stage, Qlik Replicate does not need to consider ALIAS or specific members.

Additionally, please note that according to DB2i object identifier naming conventions, object names must be 10 characters or fewer.

Hope this helps.

John.

[ernesto_costa]

Hi @john_wang,

thank you for your feedback.

Ok, so:

1. The alias are necessary for the initial load    

2. The aliases aren't needed when in CDC mode, since the events are captured from the main table, for all members.

So, regarding point #2, is there any way to know on which member the captured event was made?

This is needed in our use case because we have tables were members represent countries and we need to distinguish between them.

Will wait for your feedback.

[john_wang]

Hello @ernesto_costa ,

>> 1. The alias are necessary for the initial load

Exactly.

>> 2. The aliases aren't needed when in CDC mode, since the events are captured from the main table, for all members.

I cannot remember it clear. Let me check for you.

Regards,

John.

[john_wang]

Hi @ernesto_costa ,

No, in current version the member information was not retrieved from journal entries, so no way to know which member was changed.

Regards,

John.