Posted: Wed May 29, 2019 2:23 pm Post subject: BMC Unload
Hi, We are working on a project to unload DB2 data into a file and FTP it to AWS S3. I am running the BMC Unload utility(ADUUMAIN) now and it works fine and extracts the data in CSV format. However, I also need to have column names in the extract. Is there option in BMC Unload that does extract column names also?
Joined: 26 Nov 2002 Posts: 12375 Topics: 75 Location: San Jose
Posted: Wed May 29, 2019 9:29 pm Post subject: Re: BMC Unload
shreyas_e wrote:
Hi, We are working on a project to unload DB2 data into a file and FTP it to AWS S3. I am running the BMC Unload utility(ADUUMAIN) now and it works fine and extracts the data in CSV format. However, I also need to have column names in the extract. Is there option in BMC Unload that does extract column names also?
You can add column names as headers with adding another SQL
something like this (untested)
Code:
SELECT 'Column_Name1'
,'Column_Name2'
,'Column_Name3'
....
FROM SYSIBM.SYSDUMMY1 WITH UR;
If you want to automate this procedure, then as NIC pointed out you can retrieve the Column Names from the catalog table SYSIBM.SYSCOLUMNS
something like this
Code:
SELECT A.COL_NAME
FROM (SELECT SUBSTR(CHAR(CASE
WHEN COLNO = 1
THEN CHAR('SELECT CHAR(') ||
CHAR('''') ||
SUBSTR(NAME,1,LENGTH(RTRIM(NAME))) ||
CHAR(',') ||
CHAR('''') ||
CHAR(')')
WHEN COLNO > 1
THEN CHAR(' ,CHAR(') ||
CHAR('''') ||
SUBSTR(NAME,1,LENGTH(RTRIM(NAME))) ||
CHAR(',') ||
CHAR('''') ||
CHAR(')')
END),1,80) AS COL_NAME
,COLNO AS COL_NO
FROM SYSIBM.SYSCOLUMNS
WHERE TBNAME = 'your_table_name'
UNION
SELECT B.COL_NAME
,B.COL_NO
FROM (SELECT CHAR(' FROM SYSIBM.SYSDUMMY1' ||
' WITH UR;') AS COL_NAME
,MAX(COLNO) + 1 AS COL_NO
FROM SYSIBM.SYSCOLUMNS
WHERE TBNAME = 'your_table_name') B) A
ORDER BY A.COL_NO
;
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
