BookmarkSubscribeRSS Feed
rstoffregen
Fluorite | Level 6

I'm new as an Admin and I'm trying to assist our programming staff as they convert SAS running on z/OS to Windows using PROC SQL.  The backed DBMS is DB2 still running on z/OS. 

 

z/OS - SAS 9.4   Windows 9.4.01

 

Where would I look for info on a PROC SQL parm  DEGREE=ANY and how to convert it? 

 

On z/OS PROC SQL uses this parm apparently to great advantage (decreased elapsed time) vs Windows where so far we cant find an equivalent.  Since the SQL query is still running on z/OS DB2 we're still preplexed as to why Windows SAS wont accept the parm. 

 

Any help would be appreciated.  Thx.

 

PROC SQL Stmt on z/OS
   PROC SQL;                                    
    CONNECT TO DB2 (SSID=DSNP DEGREE=ANY);                      
     CREATE TABLE SPTRR AS
     SELECT *
     FROM CONNECTION TO DB2
     (SELECT R.STAT_TRAN_ID_NR  AS TRANID,
             DATE(R.ENTR_GP)    AS ENTRGP,
             E.EFF_DT           AS EFFDT,
             E.EXP_DT           AS EXPDT
      FROM PROD.SPTRR R, PROD.SPENT E
      WHERE R.STAT_TRAN_ID_NR  = E.STAT_TRAN_ID_NR
   /*   AND R.ENTR_GP          > '2015-05-15-00.00.01.000001'  */
        AND R.MSG_TYPE_CD      = 'I'
        AND R.MSG_CATGR_CD     = 'M'
        AND R.MSG_SUB_CATGR_CD = 'SR'
        AND R.MSG_SEQ_CD       = '0032'
        AND E.TYPE_CD          = 'LH'
        AND E.EXP_DT           IS NULL
        AND NOT EXISTS (SELECT 1
                        FROM PROD.SPENR N
                        WHERE E.STDNT_ACCT_ID_NR = N.STDNT_ACCT_ID_NR
                          AND E.SCHL_NR          = N.SCHL_NR
                          AND N.ENRL_END_DT      > E.EFF_DT)
        AND NOT EXISTS (SELECT 1
                        FROM PROD.LOAN L
                        WHERE E.STDNT_ACCT_ID_NR = L.STDNT_ACCT_ID_NR
                          AND L.TYPE_CD          = 'P')
      FOR FETCH ONLY WITH UR);
DISCONNECT FROM DB2;
%PUT &SQLXRC &SQLXMSG;
 
 
PROC SQL Stmt on Windows
   PROC SQL;                                                           
      CONNECT TO DB2 (DATABASE=DSNPROD
             user='APPL526'
  password='---user pswd here---');  
     CREATE TABLE SPTRR AS
     SELECT *
     FROM CONNECTION TO DB2
     (SELECT R.STAT_TRAN_ID_NR  AS TRANID,
             DATE(R.ENTR_GP)    AS ENTRGP,
             E.EFF_DT           AS EFFDT,
             E.EXP_DT           AS EXPDT
      FROM PROD.SPTRR R, PROD.SPENT E
      WHERE R.STAT_TRAN_ID_NR  = E.STAT_TRAN_ID_NR
   /*   AND R.ENTR_GP          > '2015-05-15-00.00.01.000001'  */
        AND R.MSG_TYPE_CD      = 'I'
        AND R.MSG_CATGR_CD     = 'M'
        AND R.MSG_SUB_CATGR_CD = 'SR'
        AND R.MSG_SEQ_CD       = '0032'
        AND E.TYPE_CD          = 'LH'
        AND E.EXP_DT           IS NULL
        AND NOT EXISTS (SELECT 1
                        FROM PROD.SPENR N
                        WHERE E.STDNT_ACCT_ID_NR = N.STDNT_ACCT_ID_NR
                          AND E.SCHL_NR          = N.SCHL_NR
                          AND N.ENRL_END_DT      > E.EFF_DT)
        AND NOT EXISTS (SELECT 1
                        FROM PROD.LOAN L
                        WHERE E.STDNT_ACCT_ID_NR = L.STDNT_ACCT_ID_NR
                          AND L.TYPE_CD          = 'P')
      FOR FETCH ONLY WITH UR);
DISCONNECT FROM DB2;
%PUT &SQLXRC &SQLXMSG;

2 REPLIES 2
rstoffregen
Fluorite | Level 6

Addtl.

 

Based on some research PROC SQL appears to use these options to run multiple processes for a task:

SAS PROC Guide - Threaded Processing Using PROC SQL  pg 218

  1. Options that exist for parallelism.
    1. DBSLICEPARM
    2. THREADS
    3. CPUCOUNT

 

It also appears the DEGREE=ANY within z/OS DB2 provides the same benefit.  So a better question might be what options need to be set or turned on in z/OS SAS to enable SQL parallelism from a Windows SAS server workload ?

 

LinusH
Tourmaline | Level 20
Your program doesn't show an attempt to use the degree option. Like you, I assume that this option is related to the data source, not the host where SAS executes. Perhaps you cod share a log and potentially require a clarification from SAS tech support.

The other options you mention related to SAS processing (as opposed to DB2). Perhaps I misinterpreted you but in the new setup you have no SAS in ZOS...?
Data never sleeps

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 750 views
  • 0 likes
  • 2 in conversation