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 tables in other regular RDBMS eg in Oracle, MySQL etc.

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 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 Qlik Replicate full load stage. More samples Creating and using ALIAS names.

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

This article describe how to overcome the limitation and setup Replicate task to replicate Multi-Member Tables.

Resolution

1. Create ALIAS for all member 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. New a Full Load + CDC task with a few tables. Certainly 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 Replicate cannot get the ALIAS metadata correctly so all STRING data type are regarded as UTF8 which leads garbage data after Full Load done. Other data types (eg number, date etc ) need not to modify.
   
   
6. Import the JSON and run the task, as same as a regular task.

 

Environment

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

Related Content

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