Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

How to populate data to DB2 tables

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: How to populate data to DB2 tables
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

Moderator Team Head


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

PostPosted: Mon Oct 06, 2008 11:21 pm    Post subject: Reply to: How to populate data to DB2 tables
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    Post subject: Reply to: How to populate data to DB2 tables
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

Site Director


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

PostPosted: Tue Oct 07, 2008 12:44 am    Post subject:
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    Post subject: Reply to: How to populate data to DB2 tables
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: 1013
Location: India

PostPosted: Tue Oct 07, 2008 9:42 am    Post subject:
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

Site Director


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

PostPosted: Tue Oct 07, 2008 9:46 am    Post subject:
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

Site Director


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

PostPosted: Tue Oct 07, 2008 9:54 am    Post subject:
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: 1013
Location: India

PostPosted: Tue Oct 07, 2008 10:10 am    Post subject:
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

Senior Member


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

PostPosted: Tue Oct 07, 2008 1:39 pm    Post subject:
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

Site Director


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

PostPosted: Wed Oct 08, 2008 12:05 am    Post subject:
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: 1013
Location: India

PostPosted: Wed Oct 08, 2008 11:58 am    Post subject:
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

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Wed Oct 08, 2008 12:50 pm    Post subject: Reply to: How to populate data to DB2 tables
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: 1013
Location: India

PostPosted: Wed Oct 08, 2008 1:41 pm    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
This topic is locked: you cannot edit posts or make replies. Unwrap the data based on delimiter X'25' bhavana yalavarthi DFSORT/ICETOOL 30 Fri Dec 09, 2016 10:25 am
No new posts Is the 'prompt' data for the DSLIST p... Willy Jensen TSO/ISPF 2 Tue Dec 06, 2016 4:38 am
This topic is locked: you cannot edit posts or make replies. How to move a long alphanumeric data ... lind sh COBOL Programming 8 Mon Dec 05, 2016 7:51 pm
No new posts ODPP(Optim Data privacy Provider) Iss... Rama kishore IBM Tools 1 Mon Nov 07, 2016 5:46 pm
No new posts Can sending 5 MB data between cobol p... Kevin Vaz CICS 12 Tue Oct 18, 2016 4:50 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us