topic Re: Requesting help with speed in reading DB2 tables? in SAS Data Management

Requesting help with speed in reading DB2 tables?

novinosrin — Thu, 19 Sep 2019 12:04:09 GMT

Hello Folks,

My team and I have a task in reading DB2 tables for a process. I am not sure how the architecture is set up. Basically, I have been given access to read DB2 tables with a libname. And the following took

23         data temp17_ar_1 ;
24         set potbnd.ar(keep=src_stm_id) ;
25         where src_stm_id in (1,30,42,48,49);
26         run;

NOTE: There were 55749594 observations read from the data set POTBND.AR.
NOTE: The data set WORK.TEMP17_AR_1 has 12757931 observations and 1 variables.
NOTE: DATA statement used (Total process time):
      real time           25:29.08
      cpu time            5:46.61

Okay, some of you may think that's fine. I hope you don't. I was once part of short term project where there was a need a access DB2 tables in a different organisation. The table sizes are comparable to the one I am currently reading. However, The execution took less than half the time. Come on, we can do better here too. Please advice. Thank you & Regards!

Re: Requesting help with speed in reading DB2 tables?

Kurt_Bremser — Thu, 19 Sep 2019 12:21:04 GMT

First of all, it seems that the where condition is not handed off to the DB.

And then you need to check observation numbers * observation size against the available network bandwidth between the servers.

After all, 4 fifths of the time SAS was waiting for data.

Re: Requesting help with speed in reading DB2 tables?

novinosrin — Thu, 19 Sep 2019 12:28:11 GMT

Thank you Sir @Kurt_Bremser for the response. I might sound silly and dumb as I admit I have no knowledge on this. Henceforth, I request your patience. However, I am gonna consolidate your points and hopefully other's(who may chime) in this thread and take it forward to the authorities concerned

BD2 tables of interest. Proc contents isn;t giving me the needed metadata info. So i did a

select count(*) and got this

POTBND DB2 Tables	Record_count
AR	55,749,594
AR_TVR_PRFL	13,984,214
AR_X_AR	15,977,287
FNC_SVC_AR	21,052,845
MSR_PRD	52,258

Not sure how I get this info-" available network bandwidth between the servers."

After all, 4 fifths of the time SAS was waiting for data.- That's no good right? 😞

Re: Requesting help with speed in reading DB2 tables?

Kurt_Bremser — Thu, 19 Sep 2019 13:35:52 GMT

As soon as you move data over a network connection, that will be the bottleneck. The goal is to maximise the (logical) throughput. So handing off the processing of data-reducing actions (where, keep) to the DB is #1.

Your network admins will be able to tell you what the maximum expectable bandwidth of your connection is, and when you compare that to your actual throughput, you'll see if further improvement is possible at all.

Your DB admins should be able to provide you with table metadata (column types and other attributes), from which you can calculate observation sizes.

Re: Requesting help with speed in reading DB2 tables?

Ksharp — Thu, 19 Sep 2019 13:38:13 GMT

I don't know how you connect to DB2.
But try some options like :

libname potbnd DB2 datasrc=xxxx readbuff=10000 bulkload dbcommit=10000;

AND

proc copy .....

Re: Requesting help with speed in reading DB2 tables?

Ksharp — Thu, 19 Sep 2019 13:38:24 GMT

I don't know how you connect to DB2.
But try some options like :

libname potbnd DB2 datasrc=xxxx readbuff=10000 bulkload dbcommit=10000;

AND

proc copy .....

Re: Requesting help with speed in reading DB2 tables?

novinosrin — Thu, 19 Sep 2019 13:43:56 GMT

Thank you Xia. All I do is, login to SAS EG, on the left hand side, in the library list where I just right click on the name, the pop asks to assign or unassign. Once I click assign, it turns "Yellow" stating it's assigned

Re: Requesting help with speed in reading DB2 tables?

novinosrin — Thu, 19 Sep 2019 19:42:45 GMT

Thank you Sir @Kurt_Bremser I will take that to DB2 Admins and other concerned folks. In general, once the SAS/ACCESS is established to DB2, could the engine not give us the performance we need, even if it takes a little hit but not to the extent of what it shows in the above LOG. Am i being reasonable to have this expectation?

here is the exact version info from the AIX server,
-------------------------------------------------------------------------------
Host: r64
Product: db2
Version: 9.4
Display Name: SAS/ACCESS Interface to DB2
Display Version: 9.4_M3

Re: Requesting help with speed in reading DB2 tables?

SASKiwi — Thu, 19 Sep 2019 20:08:08 GMT

@novinosrin - To properly test DB2 connection performance you will need to learn to define your own database connections so you can experiment with different options. Just look at the Properties of your existing DB2 connections to figure out how to do this.

I suggest you also need to try SQL passthru where you can be sure everything is running the database. Just all part of the learning process for using external databases!

Re: Requesting help with speed in reading DB2 tables?

novinosrin — Thu, 19 Sep 2019 20:13:46 GMT

Sir @SASKiwi Thought of the angel, I was gonna plug you. Just felt shy to get cheeky.

1.Properties of your existing DB2 connections

How can i do that? Is contacting DB2 folks the only way? or can it be done pro grammatically?If yes, may i get some examples?

2. For SQL pass through, would CONNECT work once the SAS EG assign through libname is already established?

Re: Requesting help with speed in reading DB2 tables?

SASKiwi — Thu, 19 Sep 2019 20:27:22 GMT

@novinosrin - In EG select a DB2 connection in your Server List, right click and select Properties. Most of what you need in a LIBNAME statement should be shown after Options.

Regarding SQL passthru CONNECT: You can refer to a LIBNAME here by doing: CONNECT USING <libref>.

Re: Requesting help with speed in reading DB2 tables?

novinosrin — Thu, 19 Sep 2019 20:34:39 GMT

Hi @Ksharp You have given me some smile



24         proc copy in=potbnd out=work memtype=data;
25            select ar;
26         run;

NOTE: Copying POTBND.AR to WORK.AR (memtype=DATA).
NOTE: BUFSIZE is not cloned when copying across different engines. System Option for BUFSIZE was used.
NOTE: There were 55749594 observations read from the data set POTBND.AR.
NOTE: The data set WORK.AR has 55749594 observations and 76 variables.
NOTE: Compressing data set WORK.AR decreased size by 74.12 percent. 
      Compressed is 327922 pages; un-compressed would require 1267037 pages.
NOTE: PROCEDURE COPY used (Total process time):
      real time           26:03.24
      cpu time            8:37.57

Much better than the datastep. Only thing is I wish I could apply a FILTER

Re: Requesting help with speed in reading DB2 tables?

novinosrin — Thu, 19 Sep 2019 22:36:45 GMT

Sir @SASKiwi Thank you for the patience. This is what I see in properties

General DB2-POTBND
Type:Library
Server: SASApp
Engine: DB2
Location: /Shared Data/DB2-POTBND

Options: schema='OTBND'
READ_ISOLATION_LEVL=UR
Datasrc=MODPOTBD

Libref: POTBND
Read Only :No
Temporary: No

Re: Requesting help with speed in reading DB2 tables?

Ksharp — Fri, 20 Sep 2019 11:29:44 GMT

Here are two options . I recommend to use @SASKiwi 's pass through .

proc sql;

connect to db2(Datasrc=MODPOTBD schema='OTBND' readbuff=10000) ;

create table want as

select *

from connection to db2 (select * from xxxxxx where ............. );

quit;

libname x db2 Datasrc=MODPOTBD schema='OTBND' readbuff=10000 ;

proc sql;

create table want as

select *

from x.xxxxxx

where ................;

quit;

Re: Requesting help with speed in reading DB2 tables?

novinosrin — Fri, 20 Sep 2019 11:36:09 GMT

Thank you @Ksharp and @SASKiwi , Let me try and update you how that goes. More importantly, Thank you for your patience and tolerance for all silly and basic stuff but that is something I am zero.

Re: Requesting help with speed in reading DB2 tables?

Patrick — Sat, 21 Sep 2019 06:13:50 GMT

@novinosrin

Whenever reading data from a database and moving the result set to SAS you want to concentrate on 2 things:

1. Push processing to the database and reduce data volumes as much as possible on the DB side

2. Move the data as efficiently over the network as possible

For 1:

If you're not using explicit pass-through SQL then use OPTIONS SASTRACE=',,,d' SASTRACELOC=SASLOG NOSTSUFFIX; before you read from the DB. This will tell you in the SAS log which part of your SAS statement the SAS/Access engine could push to the database.

If you use dataset options and functions that SAS supports for pushing into the database then the SAS/Access engine will convert these instructions into DB2 SQL and send it to the database for execution.

I personally prefer to use SQL when interfacing with a database but SAS datastep code as below should work as well.

/* option 1 */
data temp17_ar_1;
  set potbnd.ar(keep=src_stm_id where=(src_stm_id in (1,30,42,48,49)));
run;

/* option 2 */
proc sql;
  create table temp17_ar_1 as
    select src_stm_id
    from potbnd.ar
    where src_stm_id in (1,30,42,48,49)
  ;
quit;

For 2:

The bottleneck is very often the data transfer from the DB to SAS. There are many things out of your control here BUT the one thing you can change easily: Libname option READBUFF defines how many rows get transferred at once. If this options is not set explicitly then the default is 1. Increasing this value significantly leads often to much better performance.

Re: Requesting help with speed in reading DB2 tables?

novinosrin — Sat, 21 Sep 2019 12:06:30 GMT

Thank you @Patrick , Awesome points. Pity that I will have to wait till Monday to try that out alongside with help from admin. I request your patience if I may have any follow up question once I attempt interacting with the team/admin. Cheers!

Re: Requesting help with speed in reading DB2 tables?

Patrick — Sun, 22 Sep 2019 20:35:42 GMT

@novinosrin

I like your passion.

Here a few more things worth knowing.

You can use the library meta engine to assign a library that is defined in metadata as not pre-assigned (the white nodes in EG) using code as below:

/* 
  assign library defined in metadata that is not pre-assigned 
  - explicitly add option readbuff
  */
%let lref=potbnd;
libname &lref meta liburi="SASLibrary?@Libref='&lref'" metaout=data readbuff=100000;
libname &lref list;

The SAS/Access Engine can't push all SAS functions to DB2. The list of functions that can get pushed is here .

If you use any other function in a where clause or join condition then SAS will first load the data into SAS and only execute the function within SAS. The code will still work but performance will degrade because more data gets transferred over the network.

Explicit pass-through SQL lets you write SQL in the database flavor. So here SAS will just send the code as-is to the database. This allows you full control what gets sent to the DB and you can also use DB functionality not available via implicit SQL or SAS datastep code.

/* explicit pass-through SQL using a library definition to connect to a database */
proc sql;
  connect using &lref as mycon;
  select * from mycon as
  (
    select src_stm_id
    from OTBND.ar
    where src_stm_id in (1,30,42,48,49)
    with UR
  )
  ;
  disconnect from mycon;
quit;

You've posted the connection info for you library....

General DB2-POTBND
Type:Library
Server: SASApp
Engine: DB2
Location: /Shared Data/DB2-POTBND
Options: schema='OTBND'
READ_ISOLATION_LEVL=UR
Datasrc=MODPOTBD
Libref: POTBND
Read Only :No
Temporary: No

...and it looks like the DB2 schema you need is already defined in your libary. I believe you therefore could omit the DB2 schema name in the DB2 SQL part. The advantage of a one level name: If the DB2 schema ever changes then the only place which requires amendment is the metadata libname definition.

  .....
  (
    select src_stm_id
    from ar
    where src_stm_id in (1,30,42,48,49)
    with UR
  )
  .....

What above also demonstrates: A SAS Libname connects to a single database schema and that's where your code executes. If you want to join tables from different schemas then you would need two different libnames - but in such a case SAS can only push schema specific filter conditions to the DB. It can't push conditions which require access to both schemas (as SAS uses two different connections/libnames).

If you use explicit pass-through SQL then you can use a single libname and have the join in-database. You just need to use a two level name for your tables with the DB2 schema name as first part. This should work as long as the user connecting to the DB can access both schemas.

And last but not least:

I'm not sure that what I've said about READBUFF applies to DB2. Still worth trying though. From the docu:

DB2 under UNIX and PC Hosts
If you do not specify a value for this option, the default buffer size is automatically calculated based on the row length of your data. The SQLExtendedFetch API call is used

Have fun!

Re: Requesting help with speed in reading DB2 tables?

novinosrin — Mon, 23 Sep 2019 11:48:35 GMT

Good morning/Good evening Sir @Patrick @down under. Thank you for all the details. I just got in and here is the test results

22         GOPTIONS ACCESSIBLE;
23         /*testing when yellow is unassigned on the left hand side in SAS EG*/
24         
25         %let lref=potbnd;
26         libname &lref meta liburi="SASLibrary?@Libref='&lref'" metaout=data readbuff=100000;
                                                                               ________
                                                                               22
ERROR: Libref POTBND is not assigned.
ERROR: Error in the LIBNAME statement.
ERROR 22-7: Invalid option name READBUFF.
27         libname &lref list;
ERROR: Libref POTBND is not assigned.
ERROR: Error in the LIBNAME statement.

23         /*testing when yellow is assigned on the left hand side in SAS EG*/
24         %let lref=potbnd;
25         libname &lref meta liburi="SASLibrary?@Libref='&lref'" metaout=data readbuff=100000;
                                                                               ________
                                                                               22
ERROR: Libref POTBND is not assigned.
ERROR: Error in the LIBNAME statement.
ERROR 22-7: Invalid option name READBUFF.
26         libname &lref list;
ERROR: Libref POTBND is not assigned.
ERROR: Error in the LIBNAME statement.

23         proc sql;
24           connect using potbnd as mycon;
ERROR: A Connection to the META DBMS is not currently supported, or is not installed at your site.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
25           select * from mycon as
26           (
             _
             22
             76
ERROR 22-322: Expecting a name.  

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

27             select src_stm_id
28             from OTBND.ar
29             where src_stm_id in (1,30,42,48,49)
30             with UR
31           )
32           ;
33           disconnect from mycon;
NOTE: Statement not executed due to NOEXEC option.
34         quit;
NOTE: The SAS System stopped processing this step because of errors.

Any thoughts/points that I can take to the admin plz?

Re: Requesting help with speed in reading DB2 tables?

Ksharp — Mon, 23 Sep 2019 12:41:52 GMT

Then remove READBUFF= option, maybe it is not supported by DB2 .

And did you try my 'connect to db2(datasrc=.....)' ?