create around 800 helper tables with ADF

Question

create around 800 helper tables with ADF

Janice Chi 580

We have a requirement in our project to create around 800 helper tables in DB2, each with specific columns (row_hash calculation, table_name, primary column, partition column, partition value).

We have tried two approaches so far:

Using a stored procedure in DB2 with a loop to create the tables dynamically.

Using a DB2-compatible script in ADF lookup with a loop for each activity.

In both approaches, we are facing challenges:

In Approach 1, we are unable to use the loop stored procedure dynamically with only the schema_name as input.

In Approach 2, ADF treats the SQL as a single query or CALL and won’t parse BEGIN..END procedural code unless it’s inside an SP.

Given these issues, we need to:

Dynamically fetch table_name, primary key column, partition column, and partition value in a loop so we don’t have to manually create helper tables.

Input only the schema_name and have the loop/stored procedure create helper tables for all tables in that schema.

Can you please advise how this can be achieved using DB2 and ADF, and what Microsoft-recommended approach or best practice we should follow to meet this requirement? Also, if there are any known limitations or considerations, please share those.

While running the stored procedure RNMOODENG.LOOP_CREATE_HELPERS in DB2 from ADF (via JDBC), the execution failed with the error:

[SQL0204] TABLES in SYSIBM type *FILE not found (SQL State: 42704, Vendor Code: -204).

REQUEST NOT TO SEND GPT RESPONSES AS I HAVE PAID VERSION OF GPT 5 :)

Smaran Thoomu 28,310 Reputation points Microsoft External Staff Moderator

2025-08-11T18:00:58.7+00:00
Hi @Janice Chi
ADF itself cannot execute procedural DB2 logic inline, it will only treat the SQL text in a Lookup or Script activity as a single statement. Any looping, conditional, or dynamic DDL execution must be encapsulated inside a stored procedure on the DB2 side.

For your requirement:

Metadata source: The SQL0204 error is because SYSIBM.TABLES does not exist in your DB2 flavor.

On DB2 for LUW, table metadata is in SYSCAT.TABLES.

On DB2 for iSeries, it’s in QSYS2.SYSTABLES.

Adjusting the SP to query the correct catalog should resolve the metadata lookup issue.

Dynamic creation logic: Keep the loop and CREATE TABLE execution inside the SP.

SP input: only schema_name.

Inside SP: query system catalog to get table names, PKs, partition columns, and partition values.

Use dynamic SQL in a loop to create all helper tables.

ADF integration: Call the SP via the Stored Procedure activity in ADF over JDBC, passing just the schema_name. ADF doesn’t need to be aware of individual table creation steps.

Recommended aproach/Best practise:

Keep all procedural and dynamic DDL logic in the source system (DB2) via a stored procedure, and use Azure Data Factory only to orchestrate the execution with parameters. This avoids pushing complex looping logic into ADF, keeps metadata handling close to the source engine, and reduces cross-system complexity.

Limitations / considerations:

Ensure the DB2 user ADF connects with has CREATE TABLE and catalog read privileges.

Dynamic DDL can’t be batched as a single transaction in DB2, so partial runs may require cleanup or rerun logic.

For 800+ tables, creation time will depend on DB2’s DDL commit performance and schema design.

This approach keeps all procedural execution on DB2 (where it’s supported) and uses ADF only for orchestration and parameter passing. I hope this information helps. Please do let us know if you have any further queries.

Kindly consider upvoting the comment if the information provided is helpful. This can assist other community members in resolving similar issues.

Share via

create around 800 helper tables with ADF

Your answer