IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

How to populate data to DB2 tables


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
vvgoud

New User


Joined: 16 Aug 2007
Posts: 27
Location: Hyderabad

PostPosted: Mon Oct 06, 2008 11:16 pm
Reply with quote

Please can anybody suggest what is the best method for populating data into db2 tables.

We have new DB2 tables, user provided the data in a spreadsheet we need to populate the data into DB2 tables.


Thanks,
Vemula.
Back to top
View user's profile Send private message
superk

Global Moderator


Joined: 26 Apr 2004
Posts: 4652
Location: Raleigh, NC, USA

PostPosted: Mon Oct 06, 2008 11:21 pm
Reply with quote

Spreadsheet to comma (or tab or semi-colon)-delimited file, Upload, Convert into fixed-length fields (SORT), Load data into DB2?
Back to top
View user's profile Send private message
vvgoud

New User


Joined: 16 Aug 2007
Posts: 27
Location: Hyderabad

PostPosted: Mon Oct 06, 2008 11:28 pm
Reply with quote

Thanks for your response, you mean to say upload the data into mainframe file and write one time Cobol-DB2 program for loading data into DB2 tables.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Tue Oct 07, 2008 12:44 am
Reply with quote

Hello,

Quote:
write one time Cobol-DB2 program for loading data into DB2 tables.
Or use the DB2 load utility.

Your choice would depend on your requirement. Either could work.
Back to top
View user's profile Send private message
vvgoud

New User


Joined: 16 Aug 2007
Posts: 27
Location: Hyderabad

PostPosted: Tue Oct 07, 2008 3:44 am
Reply with quote

Thanks for your response
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Tue Oct 07, 2008 9:42 am
Reply with quote

Hi All,

Can i get little more explanation on this,
DATA is in EXCEL SPREADSHEET --> Has to be loaded in DB2 Tables

Quote:

Spreadsheet to comma (or tab or semi-colon)-delimited file, Upload, Convert into fixed-length fields (SORT), Load data into DB2?


Process Involved.
Conversion of EXCEL SPREADSHEET --> (TAB or SEMI-COLON) Delimited File ?
Upload it to mainframe
Convert into fixed-length fields(SORT) ?
Load data into DB2

My Doubts
1.How to convert Spreadsheet file --> TAB or SEMI-COLON Delimited file
2.After uploading the file to mainframe, How to convert the file into "fixed-length fields(SORT)"?

Sushanth Bobby
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Tue Oct 07, 2008 9:46 am
Reply with quote

Hello,

Quote:
1.How to convert Spreadsheet file --> TAB or SEMI-COLON Delimited file
On the pc, "right-click, save as" and create a tab-delimited file rather than a .xls file.

Quote:
2.After uploading the file to mainframe, How to convert the file into "fixed-length fields(SORT)"?
Pre-allocate the fixed length file on the mainframe. When the .csv file is ftp'ed into the fixed length file on the mainframe, it will be in a format that you can use directly.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Tue Oct 07, 2008 9:54 am
Reply with quote

Hello,

Having said that, i should mention that whenever i/we upload something to be loaded into a database from some pc, it is considered not validated.

We edit all inbound user-provided data before loading it into any production database.
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Tue Oct 07, 2008 10:10 am
Reply with quote

Hi Dick Scherrer,

Quote:
On the pc, "right-click, save as" and create a tab-delimited file rather than a .xls file.
WOW, never knew what those formats are used for.

Quote:
whenever i/we upload something to be loaded into a database from some pc, it is considered not validated.
We edit all inbound user-provided data before loading it into any production database.


NOT-VALIDATED - Are you talking about the validation contraints that are in the table like CHECK, REFERENTIAL & UNIQUE.

Sushanth Bobby
Back to top
View user's profile Send private message
Anuj Dhawan

Superior Member


Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India

PostPosted: Tue Oct 07, 2008 1:39 pm
Reply with quote

My preferred method is (after the table definition is up)- unload the table, get the load card from SYSPUNCH (if you are using IKJEFT01) & use BMCADM to load the data, load card does know where to chop the input stream..but you need to take care while loading the data to mainframes, there can be filelds (column values) in your excel data which are not exactly equal in length to the column definition, they should be made equal in length using "spaces".
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Wed Oct 08, 2008 12:05 am
Reply with quote

Hello,

Quote:
Are you talking about the validation contraints that are in the table like CHECK, REFERENTIAL & UNIQUE
My preference is that a production load never abend due to invalid data. External data is edited via the business rules prior to a load being attempted. Usually, there are business rules that cannot be inforced thru database constraints.
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Wed Oct 08, 2008 11:58 am
Reply with quote

Dick,

Quote:
My preference is that a production load never abend due to invalid data. External data is edited via the business rules prior to a load being attempted. Usually, there are business rules that cannot be inforced thru database constraints.


Can you elaborate on this matter.
Iam not understanding the EXPERT TERMS 'production load never abend due to invalid data', 'External data is edited via the business rules ', 'business rules that cannot be inforced thru database constraints'

Sushanth
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Wed Oct 08, 2008 12:50 pm
Reply with quote

Quote:
Iam not understanding the EXPERT TERMS 'production load never abend due to invalid data', 'External data is edited via the business rules ', 'business rules that cannot be inforced thru database constraints'


there are two concepts here...

data validity in se ( will cause abends )
- a numeric must be a numeric,
- a date must be a date ( no feb 29 on non leap years )

business rules ( will not cause abends but other misbehaviors )
- most of them at the initial stage of processing are out of range conditions
- a date i
- a number
- ... ... ...
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Wed Oct 08, 2008 1:41 pm
Reply with quote

Enrico,

So,
a production load never abend due to invalid data. External data is edited via the business rules prior to a load being attempted. - before loading the production, unload files has to be checked for(data validity & business rules).

Usually, there are business rules that cannot be inforced thru database constraints. - So is this like, suppose there is a non-pension policy number which is a valid varchar and can be loaded in a varchar column. But, it shouldn't be loaded in a pension table.


Sushanth
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Populate last day of the Month in MMD... SYNCSORT 2
No new posts Data set Rec-Cnt and Byte-Cnt Testing & Performance 2
No new posts SCOPE PENDING option -check data DB2 2
No new posts Check data with Exception Table DB2 0
No new posts JCL EXEC PARM data in C Java & MQSeries 2
Search our Forums:

Back to Top