Connect to DB2 using SQL-Server

Hello there
I have SQL 7 and ODBC-Client access to connect to DB2 (AS400). I can connect
to the DB2 database using the ODBC and VB without problems but now I want t
o use the sp_addlinkedserver to link the DB2 to my SQL database using the OD
BC. Does anybody know what
parameters I have to use and what are the values to link the DB2 to my datab
ase?.
I will appreciate any help because I've navigated for the internet looking f
or this information without luck.
regards
osotoIf you install Client Access on the server, it will install
the drivers and providers for the AS400 on the SQL Server
box so that you can use linked servers. For data source,
use the IP address of the AS400. For provider string, you
need to include the library you are using, connect timeout
setting and code page. There is some documentation for the
settings in the Client Access help files.
You can also use the OLE DB provider and follow the similar
example on books online for "OLE DB Provider for DB2"
You'd want to change the provider to IBMDA400
and set @.provstr somewhat like:
InitCat=YourLibrary;CCSID=37;PCCodePage=
1252;
Data Source=xxx.xxx.xxx.xxx
Settings will depend on how your AS400 is configured. Again,
the Client Access help files have information on the
necessary connection string settings.
-Sue
On Fri, 5 Mar 2004 08:26:07 -0800, "osoto"
<anonymous@.discussions.microsoft.com> wrote:

>Hello there
>I have SQL 7 and ODBC-Client access to connect to DB2 (AS400). I can connect to the
DB2 database using the ODBC and VB without problems but now I want to use the sp_ad
dlinkedserver to link the DB2 to my SQL database using the ODBC. Does anybody know w
hat
parameters I have to use and what are the values to link the DB2 to my database?.kred">
>I will appreciate any help because I've navigated for the internet looking
for this information without luck.
>regards
>osoto
>sqlsql

Connect to AS400 table

Hi,
Is there anyway to connect to AS400 table (something like link table feature in MS Access). OR. I have to use the DTS package and schedule it to update in intervals, this i am not preferring due to the performance issue ?

your help highly appreciate

thanks
Murali.As long as you have ODBC drivers that will allow a Windows application to access the AS400 table, you can use sp_addlinkedserver (http://msdn2.microsoft.com/en-us/library/ms190479.aspx) to allow SQL Server to access it too.

-PatP

Connect to a DSN for data

I have an AS400 system and I created a DSN to see the data on my workstation. The connection works fine. I created the samne connection on the Server that has SQL 2005 installed. What I need to do is to populate the SQL database with tables from the AS400 table but I am having trouble connecting a DSN to the SQL database.

Can anyone offer some advice or a website that could walk me through this?

Hello,

I think you should be using Integration Services for this, Reporting Services does not do any type of data transfer. IS will be able to handle this easily, one connection will be your SQL Server, the other will be a ODBC or OLE DB connection using your DSN.

Hope this helps.

Jarret

Confused about Implicit Data Conversion Error

Good Afternoon

Hope that somone can shed some light...

I am using the HitSoftware driver to pass data to an AS400 from a SQL 7 database. Data makes it fine to 2 of the 4 tables but I cannot get the syntax correct to even get out of the S/P edtitor in SQL for the other 2.

I have fields in the SQL S/P defined as VARCHAR. The target fields on the AS400 are ALPHA. So I figured the insert statement to look like:

INSERT INTO CALICOTOTESTAS400.S105Z1NM.ORDTALIB.itmrva0# (TRID30, ITNO30, ITDS30, ITYP30, INVF30, UMST30, ITAC30, UUCA30, EGNO30, RTID30)
VALUES (@.v_action, @.v_modelNumber, @.v_modelDesc, '1', 1, 'EA', @.v_acctClass, @.v_modelyear, @.v_engRevision, @.v_spectype)

However, when I try to close the S/P window I get the error:

Error 257: Implicit conversion from data type varchar to binary is not allowed. Use the CONVERT function to run this query.

So, I changed the insert statement to this:

INSERT INTO CALICOTOTESTAS400.S105Z1NM.ORDTALIB.itmrva0# (TRID30, ITNO30, ITDS30, ITYP30, INVF30, UMST30, ITAC30, UUCA30, EGNO30, RTID30)
VALUES (CONVERT(binary, @.v_action), CONVERT(binary, @.v_modelNumber), CONVERT(binary, @.v_modelDesc), CONVERT(binary, '1'), 1, CONVERT(binary, 'EA'), CONVERT(binary, @.v_acctClass), CONVERT(binary, @.v_modelyear), CONVERT(binary, @.v_engRevision), CONVERT(binary,
@.v_spectype))

Now the S/P closes without the error. But when I send it to the AS400 I get unrecognizable characters in the fields.

Then I started thinking...I am not trying to do anything with binary fields.
So I am really lost. Please help.

Thanks,
Ed 330-273-7521Do you have the DDL of the table you're inserting in to?

Seems like a column may be binary...|||The INVF30 field is zoned decimal. The 1 with no CONVERT in the insert statement is that value I am trying to use.

If I change one of the CONVERT functions from binary to char where the target field is ALPHA, the implicit conversion comes. Just doesn't seem right...if I am dealing with ALPHA and VARCHAR how is it getting an error dealing with binary.

Thanks,
Ed|||What database is on the AS400?

ALPHA?

Is there a VARALPHA?

Anyway, just for kicks, convert the varcharts to char(max length) and see what happens..

Also have you done this successfully before?

Can you compare the 2 AS400 structures to see how they're different?|||Hey There -

I do not know what kind of db is on the 400. "They" kind of said "here is that tables you need to write to and the Hit Software driver we are evaluating". I did look at the table structure and the fields are CHAR not
the mis-informed ALPHA i mentioned earlier.

So, shouldn't VARCHAR to CHAR work? I did try declaring variables defined as CHAR and then Selecting the VARCHAR values into the variables. Then used those to send to the 400. Figured CHAR going to CHAR might net a different behavior. But, I could not get out of the S/P editor window due to the implicit erorr binary message.

I have never done this before. Currently, the production system is using Symbiator to perform the data transfer task. Symbiator is going away here and they need to get the data to the 400 via an alternative means.

Ed|||Sorry for not being helpful...

but can you do a select of the data from the as400 and see what it look like?

(My guess is it's DB2)

And place the results in to a temp table...

Also you say it worked for 2 tables already...

I would ask, what's different between these tables...|||OK...

I dumped the file to a spreadsheet and it appears to be in binary. I don't know how to convert binary and have to figure out how to make it
"readable".

One of the other tables only has 1 field and it is CHAR(15). The SQL source is VARCHAR(15). Second table has 3 fields that are CHAR and the SQL sources are all VARCHAR.

I can read the data fine and did not have to convert VARCHAR to binary just to get it to go across to a CHAR field.

very bizzare and frustraing at the same time

Ed