Search code examples
sqlimportdb2etldb2-luw

How to add dynamic filename path in IBM DB2 IMPORT Command?

I am working on ETL process from CSV to DB2 using DB2 utility. The challenge is to assign dynamic file name as db2 variable in import command. Here are the sample codes,

connect to database;
CREATE OR REPLACE VARIABLE filenamePath VARCHAR(225);
SET filenamePath= concat(concat('Z:/directory/file_',ts_fmt(current timestamp - 2 DAY,'ddmmyyyy')),'.csv');
import from '@filenamePath' of DEL SKIPCOUNT 1 insert into table;
connect reset;

Here ts_fmt is a stored procedure for getting date in specific format. I am getting output for @filenamePath

If I run the below statement,

select @filenamePath FROM sysibm.sysdummy1;

i will get output like,

Z:/directory/file_13092017.csv

But for the import command, I am getting file not found error.

Solution

  • After reading from various resources, it is confirmed that we cannot use db2 variable in db2 import utility. Since import command will not read db2 variables value. So i created a system variable instead and used the same in import command. It works.

    Open DB2 CLP as administrator and Run the following commands.

    My Sample Code

    Previously I used DB2 variable for getting filename.

    Now i am using last modified file command (Windows cmd) to get the same file name. This is what i expected.