Help using Base SAS procedures

Proc SQL - Differences and/or a better way

Reply
Occasional Contributor
Posts: 9

Proc SQL - Differences and/or a better way

I running on under ZOS 1.13, DB2 V10 (was working same under DB2V9), SAS 9.3. I am creating a process to ready the DB2 system tables using an creator id and getting all the table names owned.  My second process is to generate  SQL and counting each table's rows.  The second will be a macro, replacing table names, count names, then merging all into one, or perhaps flat file not sure yet.

I am using pass through.  My thought was if I connect to Db2 over and over I am going to be rather inefficient, thus I have been trying to figure out how to do within one SQL, or at least one CONNECT to DB2 statement.

I am able to get the SQL below to run, then when I execute in my local PC SAS sessions it fails (with a few minor differences of course). 

Also if you have another way in SQL I don't mind being told, I am all about efficiency here and we have offshore that created something in REXX and it uses A LOT of time, I know using pass though like this it will be efficient.

Thanks for your time:

Mainframe works:

proc sql;                                          

CONNECT TO db2 (ssid=&db2_id);                          

    create table a as  select * from connection to db2  

   (SELECT COUNT(*) AS TABLEA                           

     FROM DB2ACPT.CLIENT_ADDRESS)                       

    QUIT;                                                

                                                        

    create table b as  select * from connection to db2  

   (SELECT COUNT(*) AS TABLEB                           

     FROM DB2ACPT.CLIENT_ADDRESS)                       

  QUIT;                

This code works, but you notice the QUIT statement is not present and will not function with the quit present..

proc sql;      

SELECT COUNT(*) AS row_cnt_a FROM a  ;

SELECT COUNT(*) AS row_cnt_b from b  ;  

QUIT;                                    


Super User
Posts: 19,822

Re: Proc SQL - Differences and/or a better way

Most Databases have something like "Dictionary" tables that contain metadata about the tables in the databases, including the table names and row counts.

For DB2

the relevant names are here:

Oracle DB2 Data Dictionary Views

Using SQL Pass through to access those tables seems more efficient to me.

Occasional Contributor
Posts: 9

Re: Proc SQL - Differences and/or a better way

Yes I know, the issue I am addressing is if you picked up the whole environment and moved it , this would validate all data were moved, I agree with what your saying but this what someone wants to be done, snapshot before, snapshot after and compare, my own thinking it would be better to compare what the metadata shows against the tables actually have , but again I don't get to choose, but thank you!

Trusted Advisor
Posts: 3,214

Re: Proc SQL - Differences and/or a better way

You are asking for better ways.

That are implicit sql using the Libname etc.

But if you have no choice because ... 1 cray men can ask more questions then 10 + wise man can answer.

Why are you asking?

---->-- ja karman --<-----
Super Contributor
Posts: 297

Re: Proc SQL - Differences and/or a better way

Love that quote .

Occasional Contributor
Posts: 9

Re: Proc SQL - Differences and/or a better way

So you are saying the LIBNAME will be more efficient?  I did see somewhere where you can code the LIBNAME so it will not disconnect from DB2 repeatedly, but that was followed by using LIBNAME SAS formats the query into what SAS knows and some of the efficiencies provided by DB2 may not be used.  What about ZIP processing etc.?

When I asked for a better way I was hoping someone had a slick way to add all the table select statements into on SQL, someone would answer why the QUIT statement works in DB2 and appears to terminate and allow me to create another table using the same connect to DB2, but in native SAS I get an error on the second QUIT.

I didn't ask for opinions on what was being done, I know exactly what is being done, I don't totally agree with it but I won't just stand back and see something written totally inefficient and costly when I know I can get it done simpler and using less CPU.  Yes Native SAS is even simpler than some complicated SQL, but this is not even complicated SQL!

Super User
Posts: 19,822

Re: Proc SQL - Differences and/or a better way

So then back to your original question.

Your pass through code works (though it appears to be missing a semi-colon to me so either you copied it wrong or mistyped) and the SAS proc SQL does not?

Implicit pass through should be just as efficient I would think, the only thing is to make sure you're running it through the server if you need to, i.e. rsubmit if required depending on your set up.

Occasional Contributor
Posts: 9

Re: Proc SQL - Differences and/or a better way

Thanks, that what I was thinking but looking at this no syntax errors........perhaps need to pass to tech support,

PC SAS

380  proc sql;

381     SELECT COUNT(*) AS row_cnt_a FROM a  ;

383     SELECT COUNT(*) AS row_cnt_b from b  ;

384     QUIT;

NOTE: PROCEDURE SQL used (Total process time):

      real time           0.01 seconds

      cpu time            0.03 seconds

RAN in Batch on the mainframe:

NOTE: AUTOEXEC processing completed.                                        

                                                                            

1           %LET DB2_ID =DB2J;                                              

2                                                                           

3          proc sql;                                                        

4                                                                           

5          CONNECT TO db2 (ssid=&db2_id);                                   

DB2 NOTE:  The QUERYNO for the EXPLAIN is 10142922    

6              create table a as  select * from connection to db2           

7                                                                           

8             (SELECT COUNT(*) AS TABLEA                                    

9               FROM DB2ACPT.CLIENT_ADDRESS)                                

10                                                                          

11            QUIT;                                                         

NOTE: Table WORK.A created, with 1 rows and 1 columns.   

12                                                                          

13             create table b as  select * from connection to db2           

14                                                                          

15            (SELECT COUNT(*) AS TABLEB                                    

2                                                          The SAS System   

                                                                            

16              FROM DB2ACPT.CLIENT_ADDRESS)                                

17                                                                          

18            QUIT;                                                         

NOTE: Table WORK.B created, with 1 rows and 1 columns.                      

                                                                            

19                                                                         

Super User
Posts: 19,822

Re: Proc SQL - Differences and/or a better way

That's the correct response based on your code, the counts are created and output to the current destination, either Listing or HTML.

5    proc sql;

6        select count(*) as row_count_a from sashelp.class;

7        select count(*) as row_count_b from sashelp.cars;

8    quit;

NOTE: PROCEDURE SQL used (Total process time):

      real time           0.04 seconds

      cpu time            0.01 seconds

If you're trying to create tables you'll need to add a create a table statement.

proc sql;

     create table want as

select count(*) as row_count_a from sashelp.class;

create table want2 as

select count(*) as row_count_b from sashelp.class;

quit;

The other possibility is that the Table A/B are being created on the mainframe/server so when you run the select on your local PC SAS it isn't seeing those tables.

Trusted Advisor
Posts: 3,214

Re: Proc SQL - Differences and/or a better way

With the Libname statement you can connect to DB2 with all db2 options you possible can imagine.

They need to be setup tuned for the tyoe of work and for your hardware and os settings. Think on bulk load, bulk read, simple in databaes processing. Its goes for buffers transfer sizes locking threading. Proc ds2 is more detailed with all datatypes.

Most work is similar you can put those in one libname and use shared connections. By that avoiding session setup overhead. The implicit pass through is normally as efficient as explicit pass through. Sql is sql. I know there are a lot people arround saying that is sas is not doing that correct. You can see what is done by using the sastrace option. It will add things that are db2 specific when needed.

The pitfall is using sas functions and sas coding that cannot be translated to DB2 sql. What can happen in that case is that all data is transferred to sas and than processed.

I have seen options also preventing this and generate an error.

With an explicit pass through you are required to define all connection settings explicitly over and over again. Than use the specific sql language of db2. A quit and the semicolon of sas are not valid anymore as you are coding db2 language.

This results  in language errors when you accidently do code sas specific language. Sas macros statements are allowed as they are working before the code is compiled.

You can do several things better with sql pass through as it allows you mixing up different db2 schemas and creating tables without data transfer.  When you are needing dedicated db2 language features you be required to code it explicitly.

I

---->-- ja karman --<-----
Occasional Contributor
Posts: 9

Re: Proc SQL - Differences and/or a better way

Jaap,  Thanks for the responses,  

Trusted Advisor
Posts: 3,214

Re: Proc SQL - Differences and/or a better way

This is the db2 specific reference documentation using pass through db2 Unix ans Windows clients.

http://support.sas.com/documentation/cdl/en/acreldb/67473/HTML/default/viewer.htm#p0to253fz8qvnrn1br...

See the difference using execute statements and plain sql in proc sql.

In your code you have split up parts using the sas statement quit; as it is a sas statement sas is still having the fingers on that.  The results are going back to sas that implies a data transfer.

The use of a proc sql statement after the quit will stop the first proc sql step losing the db2 connection. That coding from .. no connection anymore.

---->-- ja karman --<-----
Trusted Advisor
Posts: 3,214

Re: Proc SQL - Differences and/or a better way

On the mainframe you have line 5 and 6

Line 5 is telling proc sql to connect to DB2

Line 6 is telling proc sql sql to create a sas table in the sas environment

Than it continues to do a sql statement on that connection returning the count from a db2 table named db2acpt.client_adress

There is a conversion db2 to sas included not everything is running in db2.

These two instructions are not there in your converted pc code approach. It goes from line 380 to 381.

What is left is a syntax correct coded statements without any input and without output.

Runs correctly and it does nothing. No error no warning as you have that coded correct.

I expect you are needing more settings options as just that said. When lucky somebody has set that for you. When not you have to solve that first.

Defining a libname as db2acpt.client_adress to that schema is the most reliable way to do.

When you have that working it is easy coding getting the os count like reeza did for the sashelp datasets.

This should be as good as the coding with that connect instruction. SAS has to convert that to the correct db2 approach as implicit pass through.

---->-- ja karman --<-----
Ask a Question
Discussion stats
  • 12 replies
  • 582 views
  • 2 likes
  • 4 in conversation