New to SAS world...Unable to connect to DB2 on AS400 linux from SAS. What am I doing wrong?

[okonita]

Hello to you SAS Gurus,

I have a couple of questions regarding SAS DB2 connectivity.

SAS Environment: SASCompute 9.4]# ./sas -nodms
                 NOTE: SAS (r) Proprietary Software 9.4 (TS1M4)
                 NOTE: This session is executing on the Linux 3.10.0-514.2.2.el7.x86_64 (LIN X64) platform.

DB2 Environment: AS400/DB2 11.1; OS is Linux

First question, in what environment setup do I need DB2 Connect to work with SAS Explorer in other to connect to DB2 on AS400??
Do I really need DB2 connect to use SAS to connect to DB2 on AS400? Why and how do I need it? I have read severally on SAS topics such as SAS/Connect, SAS/ACCESS etc but I could not find any reference to using DB2 connect from SAS to connect to DB2 or setting up DB2 connect on the DB2 server side either. Ca the SAS guru confirm this? DB2 connect is or is not needed for SAS to connect to DB2.

The next question is really the problem. I want to connect my new SAS Explorer product to an existing AS400 running DB2. I am very new to SAS but knowlegable about DB2 and can supply pertinent information for the connection. We have installed IBM DB2 connector on the SAS server, but no one has knowledge of the command line syntax to connect the client to the DB2 server. We need help confirming that we can connect and also any configuration(s) needed on the DB2 Connector client.

Do I need DB2 client on the SAS server also?

We can get full access to the SAS Linux server that the client is installed on. Do I need to do anything on the DB2 server side?
What is the typical SAS syntax to gain connection to DB2 on AS400?

Please a complete syntax example will be highly appreciated. Any comment, suggestion and connection example will be golden. I thank you all in advance.

Below is the SAS code (and errors) that I tried to run but got no where:

1? proc sql noerrorstop;

connect to db2 (user=ABC password=xxxx database=dbname; select * from connect to db2 ( select * from connection to DB2 (select * from syscat.functions where funcschema ='saslin');

  2?

2    connect to db2 (user=ABC password=XXXX database=dbname; select * from

                                                           -

                                                           79

2  ! connect to db2 ( select * from connection to DB2

ERROR 79-322: Expecting a ).

2    connect to db2 (user=ABC password=xxxx database=dbname; select * from

2  ! connect to db2 ( select * from connection to DB2

                ---

                22

ERROR 22-322: Syntax error, expecting one of the following: GROUP, HAVING,

              ORDER, WHERE.

2    connect to db2 (user=ABC password=xxxx database=dbname; select * from

2  ! connect to db2 ( select * from connection to DB2

                ---

                76

ERROR 76-322: Syntax error, statement will be ignored.

  4? proc sql noerrorstop;

connect to db2 (user=user1 password=password1 database=database1; select * from connect to db2 ( select * from connection to DB2

(select * from syscat.functions where funcschema ='saslin');

quit;

NOTE: PROCEDURE SQL used (Total process time):

      real time           1:56.49

      cpu time            0.00 seconds

  5?

5    connect to db2 (user= password=XXXXXXX database=dbname1; select *

                                                                   -

                                                                   79

5  ! from connect to db2 ( select * from connection to DB2

ERROR 79-322: Expecting a ).

5    connect to db2 (user=user1 password=password1 database=S213732V; select *

5  ! from connect to db2 ( select * from connection to DB2

                     ---

                     22

ERROR 22-322: Syntax error, expecting one of the following: GROUP, HAVING,

              ORDER, WHERE.

5    connect to db2 (user=sasdb2 password=orange1 database=S213732V; select *

5  ! from connect to db2 ( select * from connection to DB2

                     ---

                     76

ERROR 76-322: Syntax error, statement will be ignored.

  7?

proc sql noerrorstop;

connect to db2 (user=user1 password=password1 database=database1; select * from connect to db2 ( select * from connection to DB2

(select * from syscat.functions where funcschema ='schema1');

quit;

NOTE: PROCEDURE SQL used (Total process time):

      real time           6:13.79

      cpu time            0.03 seconds

  8?

8    connect to db2 (user=user1 password=password1 database=dbname1; select *

                                                                   -

                                                                  79

8  ! from connect to db2 ( select * from connection to DB2

ERROR 79-322: Expecting a ).

8   connect to db2 (user=user1 password=password1 database=dbname1; select *

8  ! from connect to db2 ( select * from connection to DB2

                     ---

                     22

ERROR 22-322: Syntax error, expecting one of the following: GROUP, HAVING,

              ORDER, WHERE.

8    connect to db2 (user=user1 password=password1 database=dbname1; select *

8  ! from connect to db2 ( select * from connection to DB2

                     ---

                     76

ERROR 76-322: Syntax error, statement will be ignored.

10?

Thanks a million everyone

Okonita

[LaurieF · Accepted solution]

There are several ways to connect to DB2 - and you're starting on the right track. But your code is a bit off. Try this - it'll work - umm - differently!

proc sql noerrorstop;
connect to db2 (user=ABC password=xxxx database=dbname);
 select * from connection to db2 
 (select * from syscat.functions where funcschema ='saslin');
 disconnect from db2;
quit;

The connect to sets up a linkage through the SAS/DB2 engine. The select statement executes the query. The disconnect drops the connection. (Not strictly necessary, but tidy).

You can also use the DB2 engine via a ilbname statement - something along the lines of

libname db2db db2 database=dbname user=abc password=xxx;

I prefer the more explicit method, because it gives me more control about how I compose the DB2 side of the query.

[okonita]

Hello LaurieF,

Thank you for "cleaning up" my SAS code for me (First SAS code I ever wrote in 14 yrs. DB2 IT work...we learn everyday, don't we?).

I will get to try the revised version when I get with my associates on Monday afternoon. I am the DB2 guy, and the SAS guy will not be available for a while unfortunately.

You did commented on another way to connect to DB2 from SAS viz by way of LIBNAME statement. The exmple you showed is something like this:

libname db2db db2 database=dbname user=abc password=xxx;

Is db2db an arbitrary name? I mean can I just makeup that name or is it a member of reserved SAS words just like we have DB2 or Oracle reserved words?

Could you please describe with a complete example code how the finished connection code/script using LIBNAME should look like. Any and all assistance will be highly appreciated...Thanks a-million

Cna

[Ksharp]

"Do I need DB2 client on the SAS server also?"
Yes. after you make a category by DB2 client at sas side and successfully connect to DB2 by that category , sas can connect to DB2 by libname xx db2 dsn= ...  .

[okonita]

Grand Advisor Ksharp,

Thanks you for this initial coment...if you don't mind me asking, what do you mean by this "

Yes. after you make a category by DB2 client at sas side..."? Could you with example show how you make a category? I don't have the SAS guy readily avalable to me but I have to find a soluton to my problem by Monday afternon..having said that, please describe with a complete example code how the finished connection code/script should look like. Any and all assistance will be highly appreciated...Thanks a-million big guy.

[Ksharp]

You are DB2 guy ,right ?

You must know how to make category by DB2 client at SAS server side?

make a category which contains all connection of DB2 information,and test it.

If it successed, then you can connect DB2 with SAS. the category name is the datasource name.

libname xx db2 dsn=category_name ............

[okonita]

Grand Advisor, I think I am having difficulty understanding the words "make a category"  you are using to describe some DB2 preparatory task. If the words mean can I "catalog a DB2 database for remote access or create  DB2 instance, Yes I can certainly do that. Create an instance has a name but catalogin s DB2 Database does not really have a name. You just invoke DB2 update commands with various parameters and these are recorded internally - nome to refrence later in a SAS script. But if "make a category" means something else that needs to be done, then I am afraid I don't know what it is or how to do that! Something tells me "make a category" means something in DB2speak that I can relate to but I can't think of anything right now.

Could you please help?

Thanks

Okonita

[Ksharp]

Sorry. leave a long time from DB2.
I mean "catalog a DB2 database for remote access ".

[okonita]

Thank you very much Ksharp. Now it makes sense and we are on the same page. I'll take it from here...

Again thanks for your valuable assistance.

Okonita