Re: using Packed- or Zoned- with other systems

Thanks Bob!  I learned that once in a class (taught by you ;-), but if fell
out of my skull.

On 5/2/06, Bob Cozzi < cozzi@xxxxxxxxx> wrote:

Tony,

9B0 are 10i0 in RPG. Use EXTBININT keyword on the Header spec.


-Bob Cozzi
www.RPGxTools.com
RPG xTools - Enjoy programming again.

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On
Behalf Of Tony Carolla
Sent: Tuesday, May 02, 2006 5:51 PM
To: RPG programming on the AS400 / iSeries
Subject: Re: using Packed- or Zoned- with other systems

Okay, I think I see an option to make this work.  Apparently, if you do a
CREATE TABLE in SQL in the iSeries, and specify INTEGER for the data type,
it creates a 9B data type.  SQL Server then recognizes the value as an
INTEGER type.

So now I have to research how to use 9B 0 data types in RPGIV (I never
have
used them, but I have used the I data types).  Any help in this area would
be greatly appreciated, but I assume I will have to RTDM.

I wish that when they introduced support for the nice 'I' data types
inside
of programs, they included support in PFs also.

Thanks to all.

On 5/2/06, Tony Carolla < carolla@xxxxxxxxx> wrote:
>
> That's what I thought Eric, and precisely why I said I could be done
with
> this by tomorrow ;-)
>
> When I am mapping the columns of the view to the columns of the iSeries
> table, and I choose a field that is 9P 0, or 9S 0, the Type column stays
> blank.  When I click 'OK', an error states that all destination columns
must
> have a data type assigned.  I have tried using the following data
providers:
>
>
> IBM DB2 UDB for iSeries IBMDA400 OLE DB Provider
> IBM DB2 UDB for iSeries IBMDARLA OLE DB Provider
> IBM DB2 UDB for iSeries IBMDASQL OLE DB Provider
>
> This is why I assumed I must use an alternate method.
>
>
> On 5/2/06, DeLong, Eric <EDeLong@xxxxxxxxxxxxxxx> wrote:
> >
> > Tony,
> >
> > I can't understand why SQL server would even need to know about packed

> > or
> > zoned.  That's for the database to handle.  As far as MS is concerned,
> > its
> > passing a NUMERIC data type.  That's all that's needed...
> >
> > This is the same as ASCII to EBCDIC.  That conversion happens under
the
> > covers of the iSeries ODBC driver.
> >
> > Can you check which driver you're using for the DTS package?
> >
> > Eric DeLong
> > Sally Beauty Company
> > MIS-Project Manager (BSG)
> > 940-297-2863 or ext. 1863
> >
> >
> >
> > -----Original Message-----
> > From: rpg400-l-bounces@xxxxxxxxxxxx
> > [mailto: rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of Tony Carolla
> > Sent: Tuesday, May 02, 2006 5:07 PM
> > To: rpg400-l@xxxxxxxxxxxx
> > Subject: using Packed- or Zoned- with other systems
> >
> >
> > I have a program that builds a data warehouse of sorts on our iSeries.
> > Most
> > of this data was coming from the iSeries until recently, when we moved
> > our
> > imaging to the Windows platform.  I am trying to get a simple list of
> > elements from an SQL server 2005 database into an iSeries table, using

> > RPG,
> > DTS, or whatever tool I need.  This will be used by my RPGIV build
> > program
> > to build the warehouse records for reporting.
> >
> > My first attempt was to simply create a view on the SQL server with
the
> > data, create a table on the iSeries, and set up a DTS package on the
SQL
> > Server to pass the data from the view to the physical file.  The
problem
> > is
> > that the SQL server doesn't understand the format of packed or zoned
> > fields,
> > and cannot map the fields from the view to the fields in the table.  I
> > know
> > that, inside an RPGIV program, you can use the I and U data types, and
> > these
> > correspond to INT and LONG types in the PC world, but how to
accomplish
> > this
> > when the destination is a table on the iSeries, and not a program?
> >
> > I am asking for suggestions in accomplishing this.  The options I can
> > think
> > of are:
> >
> > 1)  Turn the numeric fields into character in the SQL view, pass them
as
> >
> > char, then convert inside the RPGIV program
> > 2)  Use CLI SQL to fetch the data from the view into a temporary table

> > on
> > the iSeries
> > 3)  Write a procedure/batch file to extract a CSV, and import to
iSeries
> > table.
> >
> > Any suggestions would be helpful.  Thanks!
> >
> > --
> > "Enter any 11-digit prime number to continue..."
> > --
> > This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing
> > list
> > To post a message email: RPG400-L@xxxxxxxxxxxx
> > To subscribe, unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> > or email: RPG400-L-request@xxxxxxxxxxxx
> > Before posting, please take a moment to review the archives
> > at http://archive.midrange.com/rpg400-l .
> >
> >
> > --
> > This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing
> > list
> > To post a message email: RPG400-L@xxxxxxxxxxxx
> > To subscribe, unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> > or email: RPG400-L-request@xxxxxxxxxxxx
> > Before posting, please take a moment to review the archives
> > at http://archive.midrange.com/rpg400-l.
> >
> >
>
>
> --
>
> "Enter any 11-digit prime number to continue..."
> "In Hebrew SQL, how do you use right() and left()?..." - Random Thought
> "If all you have is a hammer, all your problems begin to look like
nails"
>



--
"Enter any 11-digit prime number to continue..."
"In Hebrew SQL, how do you use right() and left()?..." - Random Thought
"If all you have is a hammer, all your problems begin to look like nails"
--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l .


--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.

--
"Enter any 11-digit prime number to continue..."
"In Hebrew SQL, how do you use right() and left()?..." - Random Thought
"If all you have is a hammer, all your problems begin to look like nails"