-1

For Eg: 1)File has

ID|Name|job|hobby|salary|hobby2

2)Data:

1|ram|architect|tennis|20000|cricket     
1|ram|architect|football|20000|gardening             
2|krish|teacher|painting|25000|cooking

3)Table:
Columns in table: ID-Name-Job-Hobby-Salary
Is it possible to load data into table as below:

1-ram-architect-tenniscricketfootbalgardening-20000    
2-krish-teacher-paintingcooking-25000

Command: db2 "Load CLIENT FROM ABC.FILE of DEL MODIFIED BY coldel0x7x keepblanks REPLACE INTO tablename(ID,Name,Job,Hobby,salary) nonrecoverable"

RavinderSingh13
  • 130,504
  • 14
  • 57
  • 93
sravanthi leela
  • 11
  • 5
  • Kindly do add your efforts(in form of code) in your question, which is highly encouraged on SO, thank you. – RavinderSingh13 Mar 02 '21 at 06:23
  • What exactly is the problem or question? LOAD and IMPORT use a line from the input file as new row in the target table. If you need to more, search for ETL. – data_henrik Mar 02 '21 at 07:43
  • you can ignore this '-' symbol. I just used that for explaining purpose. As mentioned in the file,the data is delimited with | and I what to load the data into a normal db2 table in the mentioned way that i have explained i.e i want to combine hobby and hobby2 and also if we have another row for same ID i want to add those hobbies too and load into table as one row. – sravanthi leela Mar 02 '21 at 08:15

2 Answers2

0

You cannot achieve what you think you want in a single action with either LOAD CLIENT or IMPORT.

You are asking to denormalize, and I presume you understand the consequences.

Regardless, you can use a multi-step approach, first load/import into a temporary table, and then in a second step use SQL to denormalize into the final table, before discarding the temporary table.

Or if you are adept with awk , and the data file is correctly sorted, then you can pre-process the file externally to a database before load/import.

Or use an ETL tool.

mao
  • 11,321
  • 2
  • 13
  • 29
0

You may use the INGEST command instead of LOAD. You must create the corresponding infrastructure for this command beforehand with the following command, for example:

CALL SYSINSTALLOBJECTS('INGEST', 'C', 'USERSPACE1', NULL);

Load your file afterwards with the following command:

INGEST FROM FILE ABC.FILE
FORMAT DELIMITED by '|'    
(        
  $id       INTEGER EXTERNAL
, $name     CHAR(8)
, $job      CHAR(20)
, $hobby    CHAR(20)
, $salary   INTEGER EXTERNAL
, $hobby2   CHAR(20)
)
MERGE INTO tablename
ON ID = $id
WHEN MATCHED THEN
    UPDATE SET hobby = hobby CONCAT $hobby CONCAT $hobby2
WHEN NOT MATCHED THEN
    INSERT (ID, NAME, JOB, HOBBY, SALARY) VALUES($id, $name, $job, $hobby CONCAT $hobby2, $salary);
Mark Barinstein
  • 11,456
  • 2
  • 8
  • 16
  • what if the file does not contain headers. – sravanthi leela Mar 03 '21 at 04:36
  • The file **must not** contain any headers. The $ variables are assigned by you with any naming you like. Their number and types must be compatible with the file contents. – Mark Barinstein Mar 03 '21 at 06:33
  • CALL SYSINSTALLOBJECTS('INGEST', 'C', 'USERSPACE1', NULL); could you please explain what is C,USERSPACE1 and null specify in these parameters – sravanthi leela Mar 04 '21 at 07:07
  • Look at the [SYSINSTALLOBJECTS procedure](https://www.ibm.com/support/knowledgecenter/SSEPGG_11.5.0/com.ibm.db2.luw.sql.rtn.doc/doc/r0011876.html) description in the Db2 documentation. – Mark Barinstein Mar 04 '21 at 07:43