View previous topic :: View next topic
|
Author |
Message |
vvgoud
New User
Joined: 16 Aug 2007 Posts: 27 Location: Hyderabad
|
|
|
|
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 |
|
|
superk
Global Moderator
Joined: 26 Apr 2004 Posts: 4652 Location: Raleigh, NC, USA
|
|
|
|
Spreadsheet to comma (or tab or semi-colon)-delimited file, Upload, Convert into fixed-length fields (SORT), Load data into DB2? |
|
Back to top |
|
|
vvgoud
New User
Joined: 16 Aug 2007 Posts: 27 Location: Hyderabad
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
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 |
|
|
vvgoud
New User
Joined: 16 Aug 2007 Posts: 27 Location: Hyderabad
|
|
|
|
Thanks for your response |
|
Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
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 |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
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 |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006 Posts: 6250 Location: Mumbai, India
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
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 |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
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 |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10872 Location: italy
|
|
|
|
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 |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
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 |
|
|
|