Recipes

Exit points: checking who controls network access

FTP, ODBC and data transfer reach the database without going through menus. The two SQL views for checking who controls that access.

exit-pointftpodbcretesicurezzasql

Measuring *PUBLIC before restricting it

QCRTAUT defaults to *CHANGE, so public authority is wider than anyone decided. How to measure it with SQL before restricting it.

publicdirittiautorizzazionisqlsicurezza

Turning on the audit journal and reading it with SQL

QAUDJRN is not on by default and cannot be enabled retroactively. The command to turn it on, the functions to query it, and the library they live in.

qaudjrnauditsqlsicurezzasystools

Reading a spooled file with SQL

Querying the contents of a spooled file without copying it into a physical file first.

sqlspooldb2

Exporting a file to CSV on the IFS

With the right delimiter and the right encoding, so Excel does not show odd characters.

clifscsv

Converting a CYYMMDD date in SQL

Turning the seven-digit format into a real date with a single expression.

sqldatedb2

Seeing which jobs use the most CPU

What WRKACTJOB shows on screen, but queryable and sortable.

sqljobperformance

Sending mail from a CL program

The shortest command to notify someone when a batch finishes, with no API and nothing to install.

clsmtpbatch

Reclaiming space from journal receivers

Which receivers can be deleted, read from SQL with their status, and the two commands that delete them even when they are not saved.

journalspaziobackup

Finding out who holds a lock on an object

The file will not update and the batch is waiting: two commands find the job responsible.

lockjobdiagnosi

Finding out how much space a library uses

And more to the point, which objects inside it are eating the space.

sqlspaziooggetti

Scheduling a job for the last day of the month

Without working out how many days the month has, and without getting leap years wrong.

clschedulazionebatch

The indexes the system suggests, and which to create

Db2 for i records which indexes it would have wanted, one query at a time. Creating them all is the quickest way to slow the database down.

sqldb2performanceindici