Run SQL from Windows against AS/400 1

[Dpressm]

It seems the DB2 CLP (Command Line Processor) is the way to go (other ideas???)

I downloaded and installed and configured and got the msg below. However I do not understand it what gets licensed - on the AS400 or Windows? My setup is at the bottom.

SQL8002N Connect processing failed; a valid product license was not found.

SQLSTATE=42968

db2 =>



The final error message was looked up here:

http://publib.boulder.ibm.com/infoc...sp?topic=/com.ibm.db2v7.doc/db2m0/sql8000.htm

Here is the error message text

SQL8002N
DRDA connect processing failed; a valid product license was not found.


Explanation: A valid license key could not be found and the evaluation period has expired.

User Response: Install a license key for the fully entitled version of the product. You can obtain a license key for the product by contacting your IBM representative or authorized dealer.

sqlcode: -8002





This is a link to where I downloaded the software from:

https://www6.software.ibm.com/dl/rtcl/rtcl-p

I then ran the setup and the post setup configuration as found here:

http://www-128.ibm.com/developerworks/db2/library/techarticle/0301chong/0301chong2.html

 

[ddiamond]

(other ideas???)

We use ODBC to execute sql from windows against the AS400.

 

[Dpressm]

How do you initiate the sql - I need something to run at the batch prompt.

 

[ddiamond]

I use both visual basic and ms access. They can be both launched in batch mode. We have a lot of over night processes that run without out any human intervention. I'm not saying CLP is a bad approach; I've just never tried it. What type of queries are they? Inserts/Updates/Deletes? If select queries, what do you want to do with the results?

 

[Dpressm]

Selects only - I want to push to a flat file.

 

[Kozusnik]

Is query400 out of the question? I'd create a CL with a runqry, then copytopcd and/or ftp. Use wrkjobscde and schedule the job to run whenever. Is that plausable for your situation?

Mark

 

[fredericofonseca]

Did you install DB2 Connect on that machine first?
This is a requirement when connecting to iSeries DB's



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[ddiamond]

Dpress,

We use the MS Access TransferData command to push data from AS400 pass-thru queries to windows flat files. This process runs at night via dos batch files. If you're interested in this approach I can post some examples.

- Dan

 

[Dpressm]

That would be great - thanks!!!

 

[fredericofonseca]

As an apart, if you have an iSeries you will have available to you iSeries Access for Windows

http://www-03.ibm.com/servers/eserver/iseries/access/windows/,

which allows you to use ODBC/OLEDB with any compliant language free of charge.

Depending on your iSeries licensing you may even have licenses to use the Data Transfer and 5250 emulator and the Data Transfer may be enough for your needs, but it does require a license, while the ODBC/OLEDB does not.

I may be wrong, but its even possible that having the iSeries Access installed will allow you to use the DB2 CLI you installed originally without further need for the DB2 Connect. Unfortunately I can not test that anymore.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[ddiamond]

The following access function is called from a macro to pump queries from the AS400 to windows flat files.

Function Policies()
    DoCmd.TransferText acExportFixed, "PolicyMaster", "PolicyMaster6", _
      "J:\GTO\Data\PolicyMaster.txt", False, ""
    DoCmd.TransferText acExportFixed, "PolicyEndorsement", _
      "PolicyEndorsement2", "J:\GTO\Data\PolicyEndorsement.txt", False, ""
    DoCmd.TransferText acExportFixed, "PolicyInsured", "PolicyInsured2", _
      "J:\GTO\Data\PolicyInsured.txt", False, ""
    DoCmd.TransferText acExportFixed, "PolicyCoverageCode", _
      "PolicyCoverageCode2", "J:\GTO\Data\PolicyCoverageCode.txt", False, ""
    DoCmd.Quit acSave
End Function

If your unfamiliar with VBA code, you could have done the whole thing graphically (point and click) with an access macro. Unfortunately, I can't post graphics here. In the above code, PolicyMaster6, PolicyEndorsement2, PolicyInsured2, and PolicyCoverageCode2 are the names of odbc pass-thru queries.

As for the dos bat file:
"C:\Program Files\Microsoft Office\Office\MSACCESS.EXE" J:\GTO\GeniusToOracle.mdb /X Policies
EXIT

Policies is the name of a access macro that calls the VBA function listed above. Important: don't forget the EXIT or the docmd.QUIT. Otherwise, the process won't terminate. This is actually a scaled down example from our production system which processes over 100 queries running several in parallel to boost performance.