Import DELimited File Format Files to DB2 for OS/390 Tables with DB2 Connect

Import DELimited File Format Files to DB2 for OS/390 Tables with DB2 Connect

Post by Walter SCHNEIDE » Sat, 16 Jun 2001 18:07:44



Hello,

It sounds like a simple task- but in reality it is'nt:

Various sources provide us with data in the popular DELimited file format
and we want to import this data to tables on DB2 for OS/390 by means of DB2
Connect (V6.1). The DB2 Connect IMPORT Utility supports only IXF format for
remote (DRDA) databases, while the DB2 Connect LOAD Utility does not support
DRDA Databases at all (and in turn, the DB2 for OS/390 LOAD Utility does not
support DEL files...). We tried a work-around using MS Access, but even this
requires additional Visiual Basic coding as well as installation and
licensing MS-Access on many workstations. So my question is, if there is
another simple way or any free distributed tool to achieve loading DEL files
with DB2 Connect. Any hint & tip will be appreciated...

Walter SCHNEIDER
Telekom Austria Informations Technology

 
 
 

Import DELimited File Format Files to DB2 for OS/390 Tables with DB2 Connect

Post by chris 'fufas' grac » Mon, 18 Jun 2001 13:42:12



> Hello,

> It sounds like a simple task- but in reality it is'nt:

> Various sources provide us with data in the popular DELimited file format
> and we want to import this data to tables on DB2 for OS/390 by means of DB2
> Connect (V6.1). The DB2 Connect IMPORT Utility supports only IXF format for
> remote (DRDA) databases, while the DB2 Connect LOAD Utility does not support
> DRDA Databases at all (and in turn, the DB2 for OS/390 LOAD Utility does not
> support DEL files...).

Why not set up a 'transit' database on an NT workstation or suchlike
and import into this one, then write a VB program to get the data out
of the 'transit' database and insert it into the production database?

I haven't used replication myself, but replication from the 'transit'
database to the production database  might be a possibility as well.

 
 
 

Import DELimited File Format Files to DB2 for OS/390 Tables with DB2 Connect

Post by Robert Jan » Tue, 19 Jun 2001 14:18:39


Okay, follow this through.
Use comma delimited exports from you favorite applications
FTP the file to the mainframe to get the character conversion
Have your freindly mainframe DBA code a set of control cards for the
table(s) you want, skipping the comma position between feilds
example xxx,yyy,zzz = feild1 (1,3), feild2 (5,7), feild3 (9,11), etc
load your tables using the mainframe load utility

This scenario can be a pain in the butt. It also works very well. All of the
steps involved can be automated so it very end-user proof :)

Robert Jans
DB2 for OS/390 DBA




> > Hello,

> > It sounds like a simple task- but in reality it is'nt:

> > Various sources provide us with data in the popular DELimited file
format
> > and we want to import this data to tables on DB2 for OS/390 by means of
DB2
> > Connect (V6.1). The DB2 Connect IMPORT Utility supports only IXF format
for
> > remote (DRDA) databases, while the DB2 Connect LOAD Utility does not
support
> > DRDA Databases at all (and in turn, the DB2 for OS/390 LOAD Utility does
not
> > support DEL files...).

> Why not set up a 'transit' database on an NT workstation or suchlike
> and import into this one, then write a VB program to get the data out
> of the 'transit' database and insert it into the production database?

> I haven't used replication myself, but replication from the 'transit'
> database to the production database  might be a possibility as well.

 
 
 

Import DELimited File Format Files to DB2 for OS/390 Tables with DB2 Connect

Post by Bill » Tue, 19 Jun 2001 21:09:51


Hi Robert,
    comma delimited fields are normally variable in length. As

    field1,field..........2,field3,
    field......1, field.................2, field...3

    But you could write a simple Rexx proc to pad them out to be fixed length.

Bill


> Okay, follow this through.
> Use comma delimited exports from you favorite applications
> FTP the file to the mainframe to get the character conversion
> Have your freindly mainframe DBA code a set of control cards for the
> table(s) you want, skipping the comma position between feilds
> example xxx,yyy,zzz = feild1 (1,3), feild2 (5,7), feild3 (9,11), etc
> load your tables using the mainframe load utility

> This scenario can be a pain in the butt. It also works very well. All of the
> steps involved can be automated so it very end-user proof :)

> Robert Jans
> DB2 for OS/390 DBA




> > > Hello,

> > > It sounds like a simple task- but in reality it is'nt:

> > > Various sources provide us with data in the popular DELimited file
> format
> > > and we want to import this data to tables on DB2 for OS/390 by means of
> DB2
> > > Connect (V6.1). The DB2 Connect IMPORT Utility supports only IXF format
> for
> > > remote (DRDA) databases, while the DB2 Connect LOAD Utility does not
> support
> > > DRDA Databases at all (and in turn, the DB2 for OS/390 LOAD Utility does
> not
> > > support DEL files...).

> > Why not set up a 'transit' database on an NT workstation or suchlike
> > and import into this one, then write a VB program to get the data out
> > of the 'transit' database and insert it into the production database?

> > I haven't used replication myself, but replication from the 'transit'
> > database to the production database  might be a possibility as well.

 
 
 

Import DELimited File Format Files to DB2 for OS/390 Tables with DB2 Connect

Post by Robert Jan » Thu, 21 Jun 2001 12:35:54


Yes I remembered that comma delimited was variable length after I had sent
the original note but if I remember correctly, tab delimited lines your data
up in nice rows and columns. You would just need to remember to build the
load card correctly and watch for making the feilds too long..........

Robert


> Hi Robert,
>     comma delimited fields are normally variable in length. As

>     field1,field..........2,field3,
>     field......1, field.................2, field...3

>     But you could write a simple Rexx proc to pad them out to be fixed
length.

> Bill


> > Okay, follow this through.
> > Use comma delimited exports from you favorite applications
> > FTP the file to the mainframe to get the character conversion
> > Have your freindly mainframe DBA code a set of control cards for the
> > table(s) you want, skipping the comma position between feilds
> > example xxx,yyy,zzz = feild1 (1,3), feild2 (5,7), feild3 (9,11), etc
> > load your tables using the mainframe load utility

> > This scenario can be a pain in the butt. It also works very well. All of
the
> > steps involved can be automated so it very end-user proof :)

> > Robert Jans
> > DB2 for OS/390 DBA




> > > > Hello,

> > > > It sounds like a simple task- but in reality it is'nt:

> > > > Various sources provide us with data in the popular DELimited file
> > format
> > > > and we want to import this data to tables on DB2 for OS/390 by means
of
> > DB2
> > > > Connect (V6.1). The DB2 Connect IMPORT Utility supports only IXF
format
> > for
> > > > remote (DRDA) databases, while the DB2 Connect LOAD Utility does not
> > support
> > > > DRDA Databases at all (and in turn, the DB2 for OS/390 LOAD Utility
does
> > not
> > > > support DEL files...).

> > > Why not set up a 'transit' database on an NT workstation or suchlike
> > > and import into this one, then write a VB program to get the data out
> > > of the 'transit' database and insert it into the production database?

> > > I haven't used replication myself, but replication from the 'transit'
> > > database to the production database  might be a possibility as well.

 
 
 

1. Table Limit with DB2 Connect to DB2 for OS/390

In a COBOL program running on DB2 for OS/390 V7 we execute an SQL stmt
that references more than 15 tables - no problem.  We recently took a
similar statement and ran it thru an ODBC data source on a DB2 Client
(V7) and DB2 Connect V7 w/FP6) to the same set of tables in DB2 for
OS/390 V7 and promptly received an SQL0129N error message that states:

-----------------------------------------------------------------------------
SQL0129N The statement contains too many table names (the maximum is
15).

Explanation: The SQL statement contains toomany table names. A single
SQL statement can reference up to 15 tables. Each table in any view
referenced is included in this limit. The statement cannot be
processed.

User Response: Break the SQL statement into two or more simple
statements with 15 or fewer table references.  This message is only
applicable to Version 1.2
servers and earlier, and hosts accessed through DB2 Connect.
-----------------------------------------------------------------------------

So my question is: Why does this inconsistency exist?  I thought both
DB2 Connect and DB2 for OS/390 are written to same SQL / DRDA spec.
Will this limit be increased with either DB2 Connect V8 or DB2 for
z/OS V8 ?  If not, what is the limiting product / architecture?

Thanks in advance.

Perry R. Shindle
Sr. Consultant
BearingPoint, Inc.
Harrisburg, PA

2. Jet 2.5 DLLs

3. Frontpage with DB2 Connect to OS/390 DB2 ?

4. SQL server record locking???

5. MTS and DB2 Connect to DB2 OS/390

6. stupid select question...

7. Access to DB2 for OS/390 via DB2 Connect

8. FoxPro or Access on Mac

9. DB2 OS/390 crashing after connection via DB2 Connect

10. DB2 OS/390 LOG FILES

11. SQLUDF.H file for DB2 6.1 on OS/390

12. desire to move data from OS 390 DB2 to AS/400 DB2 using DTS

13. Load data from DB2 in NT to DB2 in OS/390