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 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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