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;
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
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 ?
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.