import issue

[Pascal]

I have a file with data i'd like to import into my relational dbs.
The file looks like this:

Name age job
Tom 23 Programmer
John 48 Manager
Karin 22 Programmer
Nancy 33 Programmer
Violet 25 Secretary

My tables looks like this:

Table Person
--------------
Name
Age
JobId

Table Job
-----------
JobId
Description
Both tables are empty, and after the import i like the tables to be filled
like this:

Table Person
Name Age JobId
Tom 23 1
John 48 2
Karin 22 1
Nancy 33 1
Violet 25 3

Table Job
JobId Description
1 Programmer
2 Manager
3 Secretary

It seems a simple problem, but the only way to solve this problem as i see
it, is to write some software that will take care of it?

Or am i missing something?

--

Pascal

[Ian]

Pascal wrote:

I have a file with data i'd like to import into my relational dbs.
The file looks like this:

Name age job
Tom 23 Programmer
John 48 Manager
Karin 22 Programmer
Nancy 33 Programmer
Violet 25 Secretary

My tables looks like this:

Table Person
--------------
Name
Age
JobId

Table Job
-----------
JobId
Description
Both tables are empty, and after the import i like the tables to be filled
like this:

Table Person
Name Age JobId
Tom 23 1
John 48 2
Karin 22 1
Nancy 33 1
Violet 25 3

Table Job
JobId Description
1 Programmer
2 Manager
3 Secretary

It seems a simple problem, but the only way to solve this problem as i see
it, is to write some software that will take care of it?

This is an ETL (extract / transform / load) question. The DB2 IMPORT
and LOAD utilities handle the "L" portion of ETL, and as such are not
"smart" - they are written to be as fast as possible.

You need a tool (or a hand written program) to do this for you. You
could certainly load your 2 files into 2 "staging" tables and use SQL
to do the transformation.

[rAinDeEr]

Hi PASCAL,

You can create a stored procedure which will help you getting the
desired result.

1.) First make a temp table with the same structure as your file.

Create table schemaname.temp(Name character(),Age
integer,jobdescription character())

2.) Then import your file to the temp table

3.) Create a stored procedure which will get you the desired result.
I am not good in that, but will give you a template from which you can
proceed or some one from the group can give you an enhancement.
Drop PROCEDURE schemaname.import@
CREATE PROCEDURE schemaname.import(IN procname CHARACTER(2))
SPECIFIC schemaname.import
DYNAMIC RESULT SETS 1
P1: BEGIN

DECLARE SQLCODE INTEGER;
DECLARE SPV_ID INTEGER;

---------------------
--- Declare cursors
----------------------
DECLARE C1 CURSOR WITH HOLD FOR
SELECT * from schemaname.temp ;
----------------------
---OPEN CURSOR
-----------------------

OPEN C1;

-----------------------------
--FETCH THE VALUE OF CURSOR
------------------------------

FETCH C1 into
SPV_name,
SPV_age,
SPV_jobdescription;

WHILE SQLCODE <> 100 DO

IF (SPV_job='Programmer')
THEN
SET SPV_ID=1;
END IF;
IF (SPV_job='Manager')
THEN
SET SPV_JOBID=2;
END IF;
IF (SPV_job='Secretary')
THEN
SET SPV_ID=3;
END IF;

INSERT INTO schemaname.tableperson values(SPV_name,SPV_age,SPV_JOBID);
INSERT INTO schemaname.tablejob values(SPV_JOBID,SPV_jobdescription);

END WHILE;

CLOSE C1;
RETURN;

END P1@

Regards
Tariq Rahiman