PROC SQL WINDOWS vs zOS - PARM DEGREE=ANY

rstoffregen — Wed, 26 Jul 2017 15:41:38 GMT

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;

Re: PROC SQL WINDOWS vs zOS - PARM DEGREE=ANY

rstoffregen — Wed, 26 Jul 2017 18:28:21 GMT

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

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 ?

Re: PROC SQL WINDOWS vs zOS - PARM DEGREE=ANY

LinusH — Thu, 27 Jul 2017 05:19:09 GMT

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...?

topic PROC SQL WINDOWS vs zOS - PARM DEGREE=ANY in SAS Procedures

PROC SQL WINDOWS vs zOS - PARM DEGREE=ANY

Re: PROC SQL WINDOWS vs zOS - PARM DEGREE=ANY

Re: PROC SQL WINDOWS vs zOS - PARM DEGREE=ANY