MXG Tools and Usage

1. MXG Tools and Usage Chuck Hopf

2. Agenda • VMXGPRNT • VMXGFIND • VMXGSRCH • UTILWORK • READDB2 • UTILBLDP • VMXGSUM • ANALCNCR

3. VMXGPRNT • Utility to print any SAS dataset with labels modified to include the variable name and/or create a comma delimited output (CSV).

4. VMXGPRNT – Parameters • SP_DSET – dataset to be printed – defaults to _LAST_ • SP_NOBS – number of OBS to be printed – defaults to 20 • SP_REMV – remove * from labels in CSV file – defaults to NO

5. VMXGPRNT – Parameters • TMPPRNT – destination for a temporary dataset – on zOS it will be constructed and dynalloc’ed as a temporary dataset but on ASCII will be placed in your SASUSER directory. Defaults to TMPPRNT.SAS • BYLST – list of BY variables – defaults to a null string

6. VMXGPRNT – Parameters • VARLST – list of variables to be printed. Default is a null string which will print all variables • NOEXIMSG – suppresses various warnings/notes – default is YES • SP_OPNS – PROC PRINT options default is SPLIT=‘*’

7. VMXGPRNT – Example 1 • %VMXGPRNT(SP_DSET=PDB.DB2ACCT,SP_NOBS=3); • Print PDB.DB2ACCT

8. VMXGPRNT – Example 1

9. VMXGPRNT – Example 2 • Create a CSV file • Filename csv ‘h:\mxg\vmxgprnt.csv’; • odscsvall file=csv; • %vmxgprnt(SP_DSET=PDB.DB2ACCT,SP_NOBS=3,sp_remv=Y); • run; • odscsvall close; • run;

10. VMXGPRNT – Example 2

11. VMXGFIND • Utility that will find every OBS in every dataset where some condition is satisfied and make a copy/print the observations. • For example: • Find all obs where JOB=:’CICS’

12. VMXGFIND – Parameters • PDB= LIBNAME to be searched – default is PDB – can be 1 or many • PDBOUT= where to put the output datasets – datasets here will be named DDNAME_dataset where DDNAME is the libname where they were found

13. VMXGFIND – Parameters • KEEPIN= a list of variables that are used in the comparison • FIND= the comparison – for example… • Job=:’CICS’ • KEEPIN=STARTIME STRTTIME INTBTIME, • FIND= IF ('31JAN2010:10:11:12'DT LE STARTIME LE '31JAN2010:22:23:24'DT ) OR ('31JAN2010:10:11:12'DT LE STRTTIME LE '31JAN2010:22:23:24'DT ) • OR ('31JAN2010:10:11:12'DT LE INTBTIME LE '31JAN2010:22:23:24'DT ) ;,

14. VMXGFIND – Parameters • PRINT= default is NO • YES – print all the observations • NO – no print • xxx – print xxx observations

15. VMXGFIND • If PRINT=YES or xxx then VMXGPRNT is used to do the printing • Example 1: • %VMXGFIND(FIND=QWHSSSID=DBTB,PRINT=3);

16. VMXGFIND

17. VMXGSRCH • Utility that will find every observation in every dataset in every allocated SAS data library where the value of the observation contains some string. • Note: libraries must have been allocated either explicitly (LIBNAME statement) or by a DATA/PROC step.

18. VMXGSRCH – Parameters • LIBNAME= the libname to be searched. Default is a NULL string. _ALL_ will search all allocated SAS data libraries (they don’t have to be MXG) and anything else will search that specific LIBNAME. Only LIBNAMEs that have been opened will be found!!!!! You may need to insert a LIBNAME on zOS.

19. VMXGSRCH - Parameters • COPYTO= copy the datasets and observations that match to this LIBNAME • NOBS= the number of OBS to print – default is MAX • LOG= a large number of lines may be generated – LOG=NO suppresses them. Default is YES

20. VMXGSRCH - Parameters • VALUE – the value to search for • Results= what you want us to do • PRINT – just print the obs/datasets that match • COPYONLY – copy the datasets but don’t print • COUNT – just produce a count of datasets/obs/variables that match • LABEL – produce a list of variables/datasets where the value is in the label • FORMAT – produce a list of variables/datasets where the value is in the format

21. VMXGSRCH – Example 1 • %VMXGSRCH( LOG=NO,RESULTS=COUNT, VALUE=D2DD,LIBNAME=PDB);

22. VMXGSRCH- Example 1

23. VMXGSRCH – Example 2 • %VMXGSRCH( LOG=NO,RESULTS=PRINT,NOBS=2, VALUE=D2DD,LIBNAME=PDB);

24. VMXGSRCH – Example 2

25. VMXGSRCH – Example 3 • %VMXGSRCH( LOG=NO,RESULTS=PRINT,NOBS=2, VALUE=D2DD,LIBNAME=PDB, COPYTO=WORK);

26. VMXGSRCH – Example 3

27. VMXGSRCH – Example 4 • %VMXGSRCH( LOG=NO,RESULTS=COPYONLY, VALUE=D2DD,LIBNAME=PDB, COPYTO=WORK);

28. VMXGSRCH – Example 4

29. VMXGSRCH – Example 5 • %VMXGSRCH(VALUE=CPU,RESULTS=LABEL); • NOTE: Values are case sensitive

30. VMXGSRCH – Example 5

31. VMXGSRCH – Example 6 • VMXGSRCH(VALUE=TIME,RESULTS=FORMAT);

32. VMXGSRCH – Example 6

33. UTILWORK • Don’t understand the documentation on defining your workloads to RMFINTRV? This utility will build you a skeleton RMFINTRV member based on your TYPE72GO records.

34. UTILWORK - Parameters • PDB= may be either SMF or some libname that contains a TYPE72GO dataset. SMF is preferred since the normal _ETY72GO exit will suppress service classes with no activity in an interval. You only need to use a single RMF interval.

35. UTILWORK – Parameters • USEREPRT= YES/NO do you want to use report classes or service classes to define workloads. Strongly recommended that you use report classes since there can be many many more at no real cost.

36. UTILWORK - Example • %UTILWORK(PDB=PDB, OUTFILE=RMFINTRV, USERPRT=YES, INTERVAL=QTRHOUR)

37. UTILWORK - Example

38. READDB2 • MXG supplied macro that generates the code to read all of the different types of DB2 SMF data (all IFCIDs). It has been ‘enhanced’ to make a copy of the SMF data and allow for selection based on reading the record headers only which makes it very fast.

39. READDB2 • For a full list of parameters and usage see READDB2 member in the MXG SOURCLIB • Concentration here will be on selection parameters and copying of SMF data

40. READDB2 • SMFOUT= DDNAME to which SMF data will be copied – if blank no copy is made • COPYONLY= YES/NO – only copy SMF data do not format SAS datasets • Useful to make mini-SMF files to feed to DB2PM or send off to vendors • PDBOUT= DDNAME to which SAS datasets are written (WORK is default if left blank)

41. READDB2 • SYSTEM – list of systems • PLAN – list of plan names • AUTHID – list of authorization IDs • CORRID – list of correlation IDs • CONNID – list of connection IDs • DB2 – list of DB2 subsystems • CONNTYPE – list of connect types • TRANNAME – list of end-user transaction names • PACKAGE – list of package names

42. READDB2 • All values in lists separated by spaces • All parameters separated by commas (except the last one) • All values are automatically wild carded – that is, however many bytes are in the value is the length of the compare • SMFBEGN= earliest time in form ddmmmyy:hh:mm:ss or 10OCT08:15:00:00 • SMFEND= latest time in same form

43. READDB2 • %READDB2(TRANNAME=OLB_DISP, COPYONLY=YES,SMFOUT=SMFOUT); • Copy records where TRANNAME starts with OLB_DISP to SMFOUT DD but do not create SAS datasets • %READDB2(TRANNAME=OLB,PDB=WORK, SMFOUT=SMFOUT); • Copy records where TRANNAME starts with OLB and also place them in SAS datasets in the WORK dataset

44. UTILBLDP • UTILBLDP is a macro designed to simplify adding records to the normal MXG PDB (performance data base.) The coding in exits is not difficult if you understand it all but can be arcane to the uninitiated. • It can also be used to read multiple kinds of SMF data in a single pass of the SMF data and create the SAS datasets in WORK or in a PDB.

45. UTILBLDP • Normally the code to read an SMF record is: • %INCLUDE SOURCLIB(TYPE30); • And to read two types you might code: • %INCLUDE SOURCLIB(TYPE30); • %INCLUDE SOURCLIB(TYPE1415); • But that would cause two passes of the SMF dataset which can be very large and make this an expensive and time consuming process. • With UTILBLDP this becomes: • %UTILBLDP(USERADD=30 1415, BUILDPDB=NO,SORTOUT=NO,OUTFILE=INSTREAM); • %INCLUDE INSTREAM;

46. UTILBLDP • For documentation on all parameters and usage see the member in the MXG SOURCLIB • For our purposes there are only a few important parameters • SORTOUT=NO – suppresses sorting and writing of the data to the PDB DD. You may want to use the sort (just add a PDB DD to your JCL) as it will remove any duplicate records. • USERADD= a list of the record types you wish to read – 30 6 1415 64 70 etc.

47. UTILBLDP • OUTFILE= INSTREAM writes the data to the temporary dataset defined by the INSTREAM DD. You can then simply %INCLUDE INSTREAM to execute the code. If you want to STORE the code for future use (or just to see what the generated code looks like) route to a PDB member or a sequential dataset. • BUILDPDB=NO – suppresses the logic that builds the full MXG PDB.

48. VMXGSUM • Generalized summarization of ANY SAS dataset • Uses PROC MEANS to do summarization • SORTs data • Allows for changes in input and output data • Optimizes variables kept • Carries labels and formats thru summarization • Allows for long variable names • Allows for normalization of variables and changing time intervals

49. VMXGSUM • Common in reporting: • DATA xxxx; • SET yyyy; • PROC SORT DATA=xxxx; • PROC MEANS DATA=XXXX OUT=zzzz; • DATA final; • SET zzzz;

50. VMXGSUM • VMXGSUM is a short-hand way of coding a repetitive set of commands. • Used extensively internally in many MXG members but especially common in ASUM**** and TRND**** members.