DB2 coumn names and data types from DB2 as source Endpoint

[adbdkb]

I am defining a task with a DB2 table as source and kafka topic as a target.  I am trying to get the list of all columns of the table, and want to download it.  I can see the list when I click on Table settings and transform tab under Table settings.  But don't see a way to download that.  The table has over 400 columns.

Is it possible to do it from Qlik?  Where / how do I do it, if it is possible?

Thank you

[Heinvandenheuvel · Accepted solution]

The data is in a per table blob in the per task  dynamic_metadata.sqlite file.  The only somewhat supportable option is to use REPCTL dumpmetadata . As always start with dumpmetadata  -? 

REPCTL -d <datadir> dumpmetadata <datadir\tasks\<taskname>\dynamic_metadata.sqlite>  out_dir=<you-pick>

Now go down the  table_defs directory in the output and you will find JSON files describing each version of each table on input and output side 

In my example tables JAW and TEST renamed from source schema ATT_USER  from an endpoint <Oracle_Source> renamed to schema HEIN in target EP <Oracle_Target> after several output transformation tests this looks like:

ATT_USER__JAW__Oracle_Source__1.json
ATT_USER__TEST__Oracle_Source__1.json
HEIN__JAW__Oracle_Target__1.json
HEIN__TEST__Oracle_Target__1.json
HEIN__TEST__Oracle_Target__2.json
HEIN__TEST__Oracle_Target__3.json
HEIN__TEST__Oracle_Target__4.json

Below the sample output from a tiny/simple "JAW" table

Hope this helps,

Hein

C:\Data_6_3\tasks\Oracle_to_Oracle\20220518170032703575\table_defs>type HEIN__JAW__Oracle_Target__1.json
// Created at sequence: 3
{
        "name": "JAW",
        "owner":        "HEIN",
        "id":   1,
        "version":      1,
        "component_id": "Oracle_Target",
        "columns":      [{
                        "name": "X",
                        "id":   1,
                        "orig_db_id":   1,
                        "type": "kAR_DATA_TYPE_NUMERIC",
                        "length":       40,
                        "precision":    38,
                        "orig_db_type": 2,
                        "bind_db_type": 5,
                        "attributes":   33,
                        "flags":        3072,
                        "is_pk":        true,
                        "actual_converter_name":        "UTF-8"
                }, {
                        "name": "Y",
                        "id":   2,
                        "orig_db_id":   1,
                        "type": "kAR_DATA_TYPE_STR",
                        "length":       123,
                        "nullable":     true,
                        "orig_db_type": 1,
                        "bind_db_type": 1,
                        "attributes":   2,
                        "flags":        2048,
                        "actual_converter_name":        "UTF-8"
                }],
        "primary_key":  {
                "name": "PK_JAW",
                "segments":     [{
                                "name": "X",
                                "position":     1,
                                "id":   1
                        }],
                "pk_origin":    "primary_key_origin_source_pk",
                "is_clustered": false,
                "tablespace_name":      "USERS"
        },
        "record_size":  163,
        "record_identifier":    "RECORD_IDENTIFIER_NON_CLUSTERED_PRIMARY_KEY"
}

[Bill_Steinagle · Accepted solution]

While you are in Replicate Console on the Transform for a given Table you can click on Filter Tab as this will allow

you to use the scroll key and arrow and right click to copy all the Columns for the given Table.

Let us know if this helps.

Regards,

Bill Steinagle