SPUFI TIPS

SQL Debugging

Finding a bad character in SQL with SPUFI

You know when you have that forty lines of code you are testing in SPUFI and you get Illegal Symbol ',' and you can't find it. Well you can, those funny numbers after the message to give you a pointer.

Here is an example:


SELECT                                                                  
'A',                                                                    
'BBB' ,                                                                 
,'C'                                                                   
FROM                                                                    
SYSIBM.SYSDUMMY1                                                        
;                                                                       
---------+---------+---------+---------+---------+---------+---------+--
---------+---------+---------+---------+---------+---------+---------+--
---------+---------+---------+---------+---------+---------+---------+-
DSNT408I SQLCODE = -104, ERROR:  ILLEGAL SYMBOL ",". SOME SYMBOLS THAT MIGH
         LEGAL ARE: ( + - ? : CASE CAST USER <FLOAT> CURRENT <DECIMAL>  
         <INTEGER>                                                      
DSNT418I SQLSTATE   = 42601 SQLSTATE RETURN CODE                        
DSNT415I SQLERRP    = DSNHPARS SQL PROCEDURE DETECTING ERROR            
DSNT416I SQLERRD    = 0  0  0  -1  217  0 SQL DIAGNOSTIC INFORMATION    
DSNT416I SQLERRD    = X'00000000'  X'00000000'  X'00000000'  X'FFFFFFFF'
         X'000000D9'  X'00000000' SQL DIAGNOSTIC INFORMATION      

Look at the first SQLERRD line.  The fifth number shows the character position within you SQL with the error.  SPUFI is limited to 72 chars so pos 217 means line 4 position 1 (217/72=3, Rem = 1).

JCL Tips

Cleaning up sequence numbers in JCL

It is well known that JCL statements are from columns 1-72, but SYSIN cards are from 1-80.


Therefore, when we have Num On while editing a JCL and it has SYSIN cards in it, it is good to do UNNUM followed by NUM OFF.  It ensures that sequence number does not remain in cols 73-80 of SYSIN Card and results are predictable.

JCL - II

JCL Syntax

Now that you've run the JCL and seen that it works.

First, you'll notice that most lines start with two slashes. The two slashes mark a line as part of JCL. Lines that do not contain those slashes, such as the last two lines in this job, are usually embedded input files.

//TSOJOB  JOB CLASS=A,NOTIFY=&SYSUID,MSGCLASS=H

- This line is the job header. It defines a job called TSOJOB. 

- The CLASS parameter specifies the job's priority, the maximum amount of resources the job is allowed to consume, and so on. A is a good default in most installations, at least for the short jobs we'll use in this book.

- The NOTIFY parameter specifies that a user should be notified when the job ends. It could be the name of a user to notify, but here it is &SYSUID, which is a macro that expands to the user who submits the job.

- The MSGCLASS parameter specifies that the output of the job needs to be held. This makes it accessible afterward, as you will see in Section 1.3.5, "Viewing the Job Output."

//        EXEC PGM=IKJEFT01

This line starts an execution step—a step in the batch job that runs a program. It is possible for these steps to be named using an identifier immediately after the two slashes. However, this is a very simple job, so there is no need to identify this stage.

The program that this step executes is IKJEFT01, which is the TSO interpreter.

//SYSTSPRT DD SYSOUT=*

This line is a data definition. It defines the data stream called SYSTSPRT, which is the output of TSO. SYSOUT=* means that this data stream will go to the standard output of the job. In the next section, you will learn how to get to this output to read it.

//SYSTSIN  DD *

This line is another data definition. It defines SYSTSIN, which is the input to the TSO interpreter. The value * means that the text that follows is the data to be placed in SYSTSIN.

SEND 'Hello, World' U(ORIPOME)
/*

This is the input to the TSO interpreter. The first line is a command, the same "Hello, World" command we executed in Section 1.2.3, " 'Hello, World' from TSO." The second line, /*, is a delimiter that means the end of the file.

JCL

Entering commands from TSO is one way to accomplish tasks in z/OS, but many other ways exist. 

-   One of the most popular and powerful ways is to create files that contain lists of things to do. These lists are called          batch jobs and are written in z/OS Job Control Language (JCL).

-  It fulfills roughly the same role as shell scripting languages in UNIX.

Introduction to JCL

JCL is a language with its own unique vocabulary and syntax.

JCL to create batch jobs. A batch job is a request that z/OS will execute later.

 z/OS will choose when to execute the job and how much z/OS resources the job can have based upon the policies that the system administrator has set up. 

z/OS can manage multiple diverse workloads (jobs) based upon the service level that the installation wants. 

For example, online financial applications will be given higher priority and, therefore, more z/OS resources, and noncritical work will be given a lower priority and, therefore, fewer z/OS resources

In your batch job, you will tell z/OS this information:

• You'll give the name of your job, with a //JOB statement
• You'll specify the program you want to execute, with a //EXEC PGM=<program name>statement
• If your program uses or creates any data, you'll point to the data using a //DDstatement.

Example Job:

This job executes an IBM-provided z/OS program called IEFBR14. This is a dummy program that tells z/OS "I'm done and all is well." It requires no input and produces no output other than an indication to the operating system that it completed successfully.

//Arunams JOB CLASS=A, NOTIFY=&SYSUID,MSGGLASS=H

//                    EXEC PGM=IEFBR14

You can also run TSO as a batch job by using JCL to tell z/OS this information:

• The name of the job
• The program to run, which is the TSO interpreter IKJEFT01
• Where to get the input for IKJEFT01 and the commands that you want to execute
• Where to put the output from IKJEFT01, the output from TSO, and the commands that you execute

//TSOJOB  JOB CLASS=A,NOTIFY=&SYSUID,MSGCLASS=H
//        EXEC PGM=IKJEFT01
//SYSTSPRT DD SYSOUT=*
//SYSTSIN  DD *
SEND 'Hello, World' U(ORIPOME)
/*

Few Shell Commands

skulker [-irw] [-l logfile] directory days_old 

Removes files in directory older than specified number of days

shell script in /samples ƒ 

- copy to /bin/skulker or /usr/sbin/skulker ƒ 
- can be modified by installation ƒ 
- Protect it from hackers! (make it non-writable) 

e.g. skulker /tmp/ 100 ƒ

deletes files in /tmp older than 100 days ƒ
trailing slash follows a /tmp symlink to another directory

- use cron to schedule it to run regularly

fuser [-cfku] file ... 

- List process IDs of processes with open files ƒ
- Useful for finding the current users of a file, or a filesystem (e.g. before unmount) ƒ
- e.g. fuser -cu /usr/lpp/dfs shows who is using the containing filesystem

uptime 

-ƒ Display how long the system has been IPLed ƒ
- e.g. uptime 01:02PM up 14 day(s), 01:15, 58 users, load average: 0.00, 0.00, 0.00

Finding file opens in Unix

rbd2/u/drbd:>fuser -u $LOGS/evtlog.2016-04-12

/plex/var/trb/rbd2/dps1/logs/evtlog.2016-04-12: 33620442(DRBD)

rbd2/u/drbd:>ps -p 33620442

       PID TTY       TIME CMD

  33620442 ?         1:09 /plex/var/trb/rbd2/dps1/lib/sislib/sievtq.exe

Z/OS Unix Environment

1. Programs using OSS, is normally written in REXX or C Language.
2. Languages "Transform" into USS assembler callable services (syscalls) via the POSIX API.
3. Assembler services can be called directly too. 

Basic Terminology in Z/OS Unix Services

USS         – z/OS UNIX System Services „

Process    – program using UNIX system services „

Thread  – a unit (task) of work in a process „

Dub        – establish a z/OS UNIX environment for an address space z create process „

Fork/Spawn – methods by which a parent process creates a child process „

Syscall / System Call / Callable Service z a request by an active process for a service to be performed by z/OS UNIX System Services „

Zombie – address space with OMVS resources remaining after dubbed process terminates

Facts and Advantages of Z/OS Unix

Fast facts about z/OS UNIX

• It is a certified UNIX system and an integral element of z/OS. 
• WebSphere® Application Server, CICS®, IMS™ , Java™  Runtime, Tuxedo, DB2®, WebSphere MQ, SAP R/3, Lotus Domino, and Oracle Web Server all use z/OS UNIX.
• z/OS UNIX applications can communicate with DB2®, CICS, IMS, and WebSphere MQ. WebSphere Application Server on z/OS using the Optimized Local Adapters support (WOLA) provides direct communication between z/OS UNIX applications and Websphere Application Server applications on z/OS.
• z/OS UNIX is built for the enterprise where you can prioritize workloads for high performance when running with a mixed workload.
• There is a broad range of ISV applications ported to z/OS UNIX.
• z/OS UNIX has a hierarchical file system familiar to UNIX users.
• Applications can work with data in both  the z/OS UNIX file systems and traditional MVS™ data sets.  MVS programs can access UNIX files, and UNIX programs can access MVS data sets. 
• The SMB File and Print Server enables a distributed file sharing infrastructure for z/OS UNIX files and Windows® workstations.
• Users can choose which interface they want to use: the standard shell, 3270, or the ISPF interfaces.

Advantages of Z/OS Unix

• z/OS applications can take advantage of all the enterprise services
• Extensive support in the marketplace
• Enterprise-class applications and middleware
• Access to advanced security features of RACF®
• Access to the two-phase commit protocol provided by Resource Recovery Services (RRS)
• Workload Manager to manage the allocation of physical resources to maximize system responsiveness.
• High performance Database Access

z/OS UNIX and traditional UNIX

z/OS UNIX system	Traditional UNIX system
Manage and secure system resources from a single point. Consistently runs near 100% utilization. WLM gives UNIX applications extra resources to manage tasks.	Manage and secure each system's resources individually. A dedicated UNIX system usually runs at about 50-70% utilization. Without any job management, traditional UNIX systems can become disabled.

Z/OS Unix System Services - II

OMVS provides a shell interface where you can type UNIX commands, as shown in below figure.



By default, you type commands close to the bottom, at the ===> prompt (it is possible to configure OMVS to place the ===> prompt at the top instead).

Can we do our "Hello, World" example as a z/OS UNIX program? Sure! Let's do it in the C programming language. Start by using OEDIT, which is the ISPF editor for z/OS UNIX:

You are now in an editor that is very similar to the editor that you used to edit your JCL. This time, enter the "Hello, World" program, as shown in the below figure. 

Now compile and execute the program:

c89 –o test test.c

./test

If the compiler fails, type exit from OMVS and LOGOFF from TSO.
Then when you log back on, type 2096128 in the size field of the TSO logon panel.

Note that the C compiler is a separate product from OMVS, and your site might not have it.

The TSO Size Field

The size field in the TSO logon screen specifies the region size, the maximum amount of memory that will be allocated to you for that TSO logon. The C compiler requires a lot of memory, so you might need to increase this number from the default to about 2MB.

In JCL jobs, you can use REGION=<number> to specify the same information.

When you are done with OMVS, use exit to leave it.

Z/OS Unix Services

Many changes have occurred in the world of computing since the announcement of System/360 in 1964.

Among the many significant changes is the development of the UNIX operating system by employees at AT&T's Bell Labs in the 1960s.

Although UNIX has concepts such as processes and threads, which are analogous to z/OS concepts such as address spaces and tasks, many significant differences exist.

For example,
  - In UNIX, files are byte-oriented streams of data,
  - But in z/OS, files (data sets) are record oriented.



Within z/OS, you have a complete UNIX environment with z/OS UNIX System Services. 
This UNIX environment is integrated with the "traditional" z/OS environment. For example, you can access a z/OS UNIX file from a batch job and you can access a data set from a z/OS UNIX application.

You can enter the world of UNIX from z/OS in several ways. 

- From TSO, you can enter the z/OS UNIX environment using the OMVS command from the TSO READY prompt.

- Within the ISPF environment, you can type the command tso omvs to enter UNIX (in general, you can run any TSO command from ISPF by prefacing it with tso).

Why OMVS?

When IBM first added the UNIX environment to MVS, it was called Open Edition, with the "Open" designating this environment and set of interfaces as one that was not designed or owned by IBM.

The logical extension of this is Open MVS, which was shortened to the command OMVS. You can see vestiges of this naming convention in the OMVS command, the OMVS segment in user profiles, and the z/OS UNIX "O" commands, such as OGET, OPUT, and OEDIT.