AS/400 >> Retrieve MS SQL data from iSeries

by dtaffa@yahoo.com.au » Fri, 20 Oct 2006 13:42:49 GMT

Hi,

I am trying to connect to a MS SQL 2000 (or SQL 2005) Server to
retreive data from a database table and bring back into the iSeries for
processing. This has to be a real time process and not a overnight
down load. At the moment I am only interested in read access only.

The connection has to be initiated by the iSeries.

Thanks

David


AS/400 >> Retrieve MS SQL data from iSeries

by walker.l2 » Fri, 20 Oct 2006 17:34:37 GMT


Well you could certainly do this in Java on the iSeries (using JDBC).
And I imagine there are ODBC solutions (e.g. via PHP) you could use
too, but I don't have experience of those.

AS/400 >> Retrieve MS SQL data from iSeries

by 4.spam » Fri, 20 Oct 2006 19:33:16 GMT

Hello.

I have done this by generic java sp.
Accepting url, username, password, select statement as string it sends
this select statement to MS SQL and fills (creates if needed) the table
(or GTT) on iSeries with results of this select statement.
It works in our production and does this work well.

Sincerely,
Mark B.

AS/400 >> Retrieve MS SQL data from iSeries

by Andr?Schze » Fri, 20 Oct 2006 21:02:30 GMT

Hello,

If you are ready to pay for a third party solution you can have a look at
the Launcher SQL solution.
http://www.easycom-aura.com/us/launcher_sql.asp

Andr?



XXXX@XXXXX.COM ...

AS/400 >> Retrieve MS SQL data from iSeries

by Richard Schoen » Sat, 21 Oct 2006 22:25:03 GMT

Hello David,

One option is to write java programs that use the SQL Server JAR files.

If you're open to a commerical option, we have a product called RPG2SQL
Integrator which allows you to read/write data directly to MySQL, SQL Server
or any other ODBC/ADO compliant database from within a Cobol or RPG program.

Response time is very-fast and coding is very straightforward.

For more information and a FREE demo version, check out our web site or feel
free to contact me directly.

Regards,
Richard Schoen
RJS Software Systems Inc.
"Providing Your....iNFORMATION NOW!"
Email: XXXX@XXXXX.COM
Web Site: http://www.rjssoftware.com
Tel: (952) 898-3038
Fax: (952) 898-1781
Toll Free: (888) RJSSOFT

Similar Threads

1. Unable to retrieve performance data by Performance Tool plug-in for Iseries Navigator V5R4 - AS/400

2. Wondering - MS Query/excel and Iseries & SQL???

I have an odbc connection to our as/400 defined.  In excel, if I do
"data/import external data" (and then uncheck the use query wizard
box), it will then bring up a list of all files in my library list.
One of the options in the box that comes up with the list of files in
the ms query is what to include - tables, views, etc.  I have both
checked.

Problem; most of my logical files appear, but several also do not.  I
have a logical file that is built over multiple members in two files
(that both exist in my library list).  The problem is, these logical
files do not appear.  I don't know if it has to do with the fact that
the members say "All_active" or whether the file is a logical built
across two physical files in different libraries.  Does anybody know
why this would be?  Could it perhaps be that there are too many
records???  

The file description is shown at the end of this message; as I said,
this logical file does not show up in the microsoft query list even
though other logicals do - I am thinking it is either the members
parameter(s) or the fact that it's two files from two different
libraries, even though both are in my library list, or that there are
so many records....  

If anyone can tell me why this is happening and if there is a
workaround I would appreciate it.  I could maybe create "aliases" for
all the members and do a UNION (I'm not too proficient with SQL), but
I don't know if unions work across multiple libraries?

If anyone has encountered something similar and has a solution I would
be most grateful, or for any other suggestions.  I have marked the
things I am wondering if could be the issues as to why the file
doesn't show up...as I said, other logical files do.

thanks,
ga

Here is the file dds:

 5722SS1 V5R4M0  060210               Display File Description
8/02/07  12:45:16       Page    1
   File  . . . . . . . . . . . : SSALMSTZ
     Library . . . . . . . . . : CONTROLFIL
   Type of information . . . . : *ALL
   File attributes . . . . . . : *ALL
   System  . . . . . . . . . . : *LCL
   Processor . . . . . . . . . : IBM AS/400 Display File Description
Processor
 File . : SSALMSTZ     Library . : CONTROLFIL   Type of file . :
Logical *DATA             Auxiliary Storage Pool ID . : 00001
                                    Data Base File Attributes
   Externally described file . . . . . . . . . :            Yes
   File level identifier . . . . . . . . . . . :
1050424074438
   Creation date . . . . . . . . . . . . . . . :            04/24/05
   Text 'description'  . . . . . . . . . . . . : TEXT       Sales
Ticket Master LF (All Data)               TM
   Distributed file  . . . . . . . . . . . . . :            No
   Partitioned SQL Table . . . . . . . . . . . :            No
   DBCS capable  . . . . . . . . . . . . . . . :            No
   Maximum members . . . . . . . . . . . . . . : MAXMBRS    *NOMAX
   Number of triggers  . . . . . . . . . . . . :                0
   Number of members . . . . . . . . . . . . . :                2
   Access path maintenance . . . . . . . . . . : MAINT      *IMMED
   Access path recovery  . . . . . . . . . . . : RECOVER    *NO
   Force keyed access path . . . . . . . . . . : FRCACCPTH  *NO
   Preferred storage unit  . . . . . . . . . . : UNIT       *ANY
   Record format selector program  . . . . . . : FMTSLR     *NONE
   Records to force a write  . . . . . . . . . : FRCRATIO   *NONE
   Maximum file wait time  . . . . . . . . . . : WAITFILE   *IMMED
   Maximum record wait time  . . . . . . . . . : WAITRCD       60
   With check option . . . . . . . . . . . . . :            NONE
   Allow read operation  . . . . . . . . . . . :            Yes
   Allow write operation . . . . . . . . . . . :            Yes
   Allow update operation  . . . . . . . . . . : ALWUPD     *YES
   Allow delete operation  . . . . . . . . . . : ALWDLT     *YES
   Record format level check . . . . . . . . . : LVLCHK     *YES
   Access path . . . . . . . . . . . . . . . . :            Keyed
   Access path size  . . . . . . . . . . . . . : ACCPTHSIZ  *MAX1TB
   Access path logical page size . . . . . . . : PAGESIZE   *KEYLEN
   Maximum key length  . . . . . . . . . . . . :                4
   Maximum record length . . . . . . . . . . . :              179
 5722SS1 V5R4M0  060210               Display File Description
8/02/07  12:45:16       Page    2
 File . : SSALMSTZ     Library . : CONTROLFIL   Type of file . :
Logical *DATA             Auxiliary Storage Pool ID . : 00001
                                     Access Path Description
   Access path maintenance . . . . . . . . . . : MAINT      *IMMED
   Unique key values required  . . . . . . . . : UNIQUE     No
   Key order . . . . . . . . . . . . . . . . . :            Not
specified
   Select/omit specified . . . . . . . . . . . :            No
   Access path journaled . . . . . . . . . . . :            No
   Access path . . . . . . . . . . . . . . . . :            Keyed
   Number of key fields  . . . . . . . . . . . :              1
   Record format . . . . . . . . . . . . . . . :            SSALMST1
     Key field . . . . . . . . . . . . . . . . :            TMTICK
       Sequence  . . . . . . . . . . . . . . . :            Ascending
       Sign specified  . . . . . . . . . . . . :            SIGNED
       Zone/digit specified  . . . . . . . . . :            *NONE
       Alternative collating sequence  . . . . :            No
   Record format . . . . . . . . . . . . . . . :            SSALMST1
     Key field . . . . . . . . . . . . . . . . :            TMTICK
       Sequence  . . . . . . . . . . . . . . . :            Ascending
       Sign specified  . . . . . . . . . . . . :            SIGNED
       Zone/digit specified  . . . . . . . . . :            *NONE
       Alternative collating sequence  . . . . :            No
     Files accessed by logical file              PFILE
        File               Library              LF Format
        SSALMST            CONTROLFIL           SSALMST1  ????????
        P.SSMST            CONTROLHIS           SSALMST1  ????????
   Sort Sequence . . . . . . . . . . . . . . . : SRTSEQ     *HEX
   Language identifier . . . . . . . . . . . . : LANGID     ENU
 5722SS1 V5R4M0  060210               Display File Description
8/02/07  12:45:16       Page    3
 File . : SSALMSTZ     Library . : CONTROLFIL   Type of file . :
Logical *DATA             Auxiliary Storage Pool ID . : 00001
                                        Member Description
   Member  . . . . . . . . . . . . . . . . . . : MBR        ALL_ACTIVE
     Member level identifier . . . . . . . . . :
1060831203742
     Member creation date  . . . . . . . . . . :            08/31/06
     Text 'description'  . . . . . . . . . . . : TEXT
     Expiration date for member  . . . . . . . : EXPDATE    *NONE
     Access path maintenance . . . . . . . . . : MAINT      *IMMED
     Access path recovery  . . . . . . . . . . : RECOVER    *NO
     Preferred storage unit  . . . . . . . . . : UNIT       *ANY
     Record format selector program  . . . . . : FMTSLR     *NONE
     Records to force a write  . . . . . . . . : FRCRATIO   *NONE
     Share open data path  . . . . . . . . . . : SHARE      *NO
     Access Path Activity Statistics . . . . . :
       Access path logical reads . . . . . . . :
381
       Access path physical reads  . . . . . . :
       Index size  . . . . . . . . . . . . . . :
34631680
       Access path valid . . . . . . . . . . . :            Yes
       Implicit access path sharing  . . . . . :            No
       Access path journaled . . . . . . . . . :            No
     Last change date/time . . . . . . . . . . :            09/18/06
07:10:18
     Last save date/time . . . . . . . . . . . :            08/02/07
01:48:55
     Last restore date/time  . . . . . . . . . :
     Last used date  . . . . . . . . . . . . . :            08/02/07
     Days used count . . . . . . . . . . . . . :              322
       Reset date  . . . . . . . . . . . . . . :
     Number of data members  . . . . . . . . . :              9
     Based on file . . . . . . . . . . . . . . :            SSALMST
       Library . . . . . . . . . . . . . . . . :            CONTROLFIL
       Member  . . . . . . . . . . . . . . . . :            SSALMST
       Logical file format . . . . . . . . . . :            SSALMST1
       Number of index entries . . . . . . . . :                    91
     Based on file . . . . . . . . . . . . . . :            SSALMST
       Library . . . . . . . . . . . . . . . . :            CONTROLFIL
       Member  . . . . . . . . . . . . . . . . :            THIS_YTD
       Logical file format . . . . . . . . . . :            SSALMST1
       Number of index entries . . . . . . . . :                149110
     Based on file . . . . . . . . . . . . . . :            P.SSMST
       Library . . . . . . . . . . . . . . . . :            CONTROLHIS
       Member  . . . . . . . . . . . . . . . . :            YEAR_00
       Logical file format . . . . . . . . . . :            SSALMST1
       Number of index entries . . . . . . . . :                146911
     Based on file . . . . . . . . . . . . . . :            P.SSMST
       Library . . . . . . . . . . . . . . . . :            CONTROLHIS
       Member  . . . . . . . . . . . . . . . . :            YEAR_01
       Logical file format . . . . . . . . . . :            SSALMST1
       Number of index entries . . . . . . . . :                169997
     Based on file . . . . . . . . . . . . . . :            P.SSMST
       Library . . . . . . . . . . . . . . . . :            CONTROLHIS
       Member  . . . . . . . . . . . . . . . . :            YEAR_02
       Logical file format . . . . . . . . . . :            SSALMST1
       Number of index entries . . . . . . . . :                164736
 5722SS1 V5R4M0  060210               Display File Description
8/02/07  12:45:16       Page    4
 File . : SSALMSTZ     Library . : CONTROLFIL   Type of file . :
Logical *DATA             Auxiliary Storage Pool ID . : 00001
                                        Member Description
     Based on file . . . . . . . . . . . . . . :            P.SSMST
       Library . . . . . . . . . . . . . . . . :            CONTROLHIS
       Member  . . . . . . . . . . . . . . . . :            YEAR_03
       Logical file format . . . . . . . . . . :            SSALMST1
       Number of index entries . . . . . . . . :                159938
     Based on file . . . . . . . . . . . . . . :            P.SSMST
       Library . . . . . . . . . . . . . . . . :            CONTROLHIS
       Member  . . . . . . . . . . . . . . . . :            YEAR_04
       Logical file format . . . . . . . . . . :            SSALMST1
       Number of index entries . . . . . . . . :                154359
     Based on file . . . . . . . . . . . . . . :            P.SSMST
       Library . . . . . . . . . . . . . . . . :            CONTROLHIS
       Member  . . . . . . . . . . . . . . . . :            YEAR_05
       Logical file format . . . . . . . . . . :            SSALMST1
       Number of index entries . . . . . . . . :                154487
     Based on file . . . . . . . . . . . . . . :            P.SSMST
       Library . . . . . . . . . . . . . . . . :            CONTROLHIS
       Member  . . . . . . . . . . . . . . . . :            YEAR_06
       Logical file format . . . . . . . . . . :            SSALMST1
       Number of index entries . . . . . . . . :                160285
   Member  . . . . . . . . . . . . . . . . . . : MBR        ALL_DELETE
     Member level identifier . . . . . . . . . :
1060831203804
     Member creation date  . . . . . . . . . . :            08/31/06
     Text 'description'  . . . . . . . . . . . : TEXT
     Expiration date for member  . . . . . . . : EXPDATE    *NONE
     Access path maintenance . . . . . . . . . : MAINT      *IMMED
     Access path recovery  . . . . . . . . . . : RECOVER    *NO
     Preferred storage unit  . . . . . . . . . : UNIT       *ANY
     Record format selector program  . . . . . : FMTSLR     *NONE
     Records to force a write  . . . . . . . . : FRCRATIO   *NONE
     Share open data path  . . . . . . . . . . : SHARE      *NO
     Access Path Activity Statistics . . . . . :
       Access path logical reads . . . . . . . :
       Access path physical reads  . . . . . . :
       Index size  . . . . . . . . . . . . . . :
266240
       Access path valid . . . . . . . . . . . :            Yes
       Implicit access path sharing  . . . . . :            No
       Access path journaled . . . . . . . . . :            No
     Last change date/time . . . . . . . . . . :            09/18/06
07:10:31
     Last save date/time . . . . . . . . . . . :            08/02/07
01:48:55
     Last restore date/time  . . . . . . . . . :
     Last used date  . . . . . . . . . . . . . :            08/02/07
     Days used count . . . . . . . . . . . . . :              317
       Reset date  . . . . . . . . . . . . . . :
     Number of data members  . . . . . . . . . :              9
     Based on file . . . . . . . . . . . . . . :            SSALMST
       Library . . . . . . . . . . . . . . . . :            CONTROLFIL
       Member  . . . . . . . . . . . . . . . . :            DLT_TODAY
       Logical file format . . . . . . . . . . :            SSALMST1
       Number of index entries . . . . . . . . :                     1
 5722SS1 V5R4M0  060210               Display File Description
8/02/07  12:45:16       Page    5
 File . : SSALMSTZ     Library . : CONTROLFIL   Type of file . :
Logical *DATA             Auxiliary Storage Pool ID . : 00001
                                        Member Description
     Based on file . . . . . . . . . . . . . . :            SSALMST
       Library . . . . . . . . . . . . . . . . :            CONTROLFIL
       Member  . . . . . . . . . . . . . . . . :            DLT_YTD
       Logical file format . . . . . . . . . . :            SSALMST1
       Number of index entries . . . . . . . . :                   871
     Based on file . . . . . . . . . . . . . . :            P.SSMST
       Library . . . . . . . . . . . . . . . . :            CONTROLHIS
       Member  . . . . . . . . . . . . . . . . :            DLT_00
       Logical file format . . . . . . . . . . :            SSALMST1
       Number of index entries . . . . . . . . :                   938
     Based on file . . . . . . . . . . . . . . :            P.SSMST
       Library . . . . . . . . . . . . . . . . :            CONTROLHIS
       Member  . . . . . . . . . . . . . . . . :            DLT_01
       Logical file format . . . . . . . . . . :            SSALMST1
       Number of index entries . . . . . . . . :                   886
     Based on file . . . . . . . . . . . . . . :            P.SSMST
       Library . . . . . . . . . . . . . . . . :            CONTROLHIS
       Member  . . . . . . . . . . . . . . . . :            DLT_02
       Logical file format . . . . . . . . . . :            SSALMST1
       Number of index entries . . . . . . . . :                   604
     Based on file . . . . . . . . . . . . . . :            P.SSMST
       Library . . . . . . . . . . . . . . . . :            CONTROLHIS
       Member  . . . . . . . . . . . . . . . . :            DLT_03
       Logical file format . . . . . . . . . . :            SSALMST1
       Number of index entries . . . . . . . . :                   769
     Based on file . . . . . . . . . . . . . . :            P.SSMST
       Library . . . . . . . . . . . . . . . . :            CONTROLHIS
       Member  . . . . . . . . . . . . . . . . :            DLT_04
       Logical file format . . . . . . . . . . :            SSALMST1
       Number of index entries . . . . . . . . :                   887
     Based on file . . . . . . . . . . . . . . :            P.SSMST
       Library . . . . . . . . . . . . . . . . :            CONTROLHIS
       Member  . . . . . . . . . . . . . . . . :            DLT_05
       Logical file format . . . . . . . . . . :            SSALMST1
       Number of index entries . . . . . . . . :                   827
     Based on file . . . . . . . . . . . . . . :            P.SSMST
       Library . . . . . . . . . . . . . . . . :            CONTROLHIS
       Member  . . . . . . . . . . . . . . . . :            DLT_06
       Logical file format . . . . . . . . . . :            SSALMST1
       Number of index entries . . . . . . . . :                   865
 5722SS1 V5R4M0  060210               Display File Description
8/02/07  12:45:16       Page    6
 File . : SSALMSTZ     Library . : CONTROLFIL   Type of file . :
Logical *DATA             Auxiliary Storage Pool ID . : 00001
                                         Record Format List
                      Record  Format Level
 Format       Fields  Length  Identifier
 SSALMST1        45     179   45EBD1E0D15D7
    Text . . . :  Sales Ticket Master
 SSALMST1        45     179   45EBD1E0D15D7
    Text . . . :  Sales Ticket Master
    Total number of formats  . . . . . . . . . :           2
    Total number of fields . . . . . . . . . . :          90
    Total record length  . . . . . . . . . . . :         358
 5722SS1 V5R4M0  060210               Display File Description
8/02/07  12:45:16       Page    7
 File . : SSALMSTZ     Library . : CONTROLFIL   Type of file . :
Logical *DATA             Auxiliary Storage Pool ID . : 00001
                                           Member List
                              Source Creation    Last Change
  Member           Size        Type  Date       Date     Time
  ALL_ACTIVE         34631680        08/31/06 09/18/06 07:10:18
    Text:
  ALL_DELETE           266240        08/31/06 09/18/06 07:10:31
    Text:
  Total number of members  . . . . . . . . . :                 2
  Total number of members not available  . . :                 0
  Total of member sizes  . . . . . . . . . . :          34897920

ga
 XXXX@XXXXX.COM 

3. iSeries programs to update MS SQL Tables - AS/400

4. iSeries programs to update MS SQL Tables

Do a search on this group for
ISERIES / AS/400 SQ30082 you will find a
lot of information on what you're trying to do. Get the SQL manual and
make sure you get the SQL message guide. In the end our project  a DDM
file took about 10 seconds and we were done.

5. data transfer from MS SQL Server - AS/400

6. data migration question - MS SQL to DB2

We are trying to migrate a MS SQL server app to DB2 8.1 Linux  platform.
Our database has got about 300+tables with total size - 150 GB

We are using  MS SQL's  BCP utility  to extract data  from MS SQL's tables
and loading into DB2
using  DB2's   LOAD utility.  There are tons of   colums of floating point
types (singe precion & double precision types)
in the database  and when extracted using BCP,  it generates data only upto
17 digits. In other words,  data for
floating point data types is not being copied with 100 %accuracy  with BCP &
LOAD approach.

We though of using  MS SQL's  DTS data migration utitliy  but it is
practilcally impossible to migrate huge amounts
of data using DTS,  as it is too slow.

Any suggestions are greatly appreciated.

Thanks
Murty



7. SQL Query from iSeries Data Transfer

8. Retrieve data by Self join via SQL under MS access VBA

Hi,

I was confused by this issue for awhile. Any comments will be highly
appreciated!!!

I have a tabel (which is linked to another back-end mdb file in harddisk)
with several fields and I'd like to retrieve sorted data to an Excel workbook
via ADO + SQL
Part of the code is:

Dim mySQL As String
Dim myrecordset As New ADODB.Recordset

"SELECT Output.Dayin, Output.Shiftin, Output.Sequence, Output.Die, Output.
Diameter, Output.Machine, Output.Employeein, Output_1.Die, Output_1.Diameter,
Output.Meterin FROM [Output] INNER JOIN ([Output] AS Output_1) ON (Output.
Dayin=Output_1.Dayout) AND (Output.Meterin=Output_1.Meterout) AND (Output.
Shiftin=Output_1.Shiftout) AND (Output.Machine=Output_1.Machine) AND (Output.
Employeein=Output_1.Employeeout)"

DoCmd.SetWarnings False
myrecordset.Open mySQL, CurrentProject.Connection, adOpenDynamic
DoCmd.SetWarnings True
Dim myExcel  As Excel.Application
Dim myBook  As Excel.Workbook
Dim mysheet As Excel.Worksheet

Set myExcel = New Excel.Application
myExcel.Visible = False
Set myBook = myExcel.Workbooks.Add
Set mysheet = myBook.Worksheets(1)
mysheet.Range("a2").CopyFromRecordset myrecordset
myrecordset.Close

when I run the macro, an warning message pops up which reads "Run-time error
'-2147467259' (80004005): method 'open' of object '_Recordset' failed"

Strangely, I created a report and set the recordsource with the same SQL
string as following. However, it works!!!
Is Access report and Access VBA work differently regarding SQL please???
Thanks guys!

Private Sub Report_Open(Cancel As Integer)
Me.RecordSource = "SELECT Output.Dayin, Output.Shiftin, Output.Sequence,
Output.Die, Output.Diameter, Output.Machine, Output.Employeein, Output_1.Die,
Output_1.Diameter, Output.Meterin FROM [Output] INNER JOIN ([Output] AS
Output_1) ON (Output.Dayin=Output_1.Dayout) AND (Output.Meterin=Output_1.
Meterout) AND (Output.Shiftin=Output_1.Shiftout) AND (Output.Machine=Output_1.
Machine) AND (Output.Employeein=Output_1.Employeeout)"
End sub