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"