If you need help with any of the techniques described below, feel free to contact our support team.
What are CoolSpools Database variables?
The CoolSpools Database commands that convert database files support a number of special values known as CoolSpools Database variables on certain command parameters. Those special values are replaced at run time with a piece of data, for example a database file attribute.
Each CoolSpools Database variable consists of a pre-defined variable name from the list below enclosed in the markers <: … >; (start of variable marker = left-hand angle bracket followed by a colon, end of variable marker = colon followed by a right-hand angle bracket).
You can define a different pair of markers from <: and >; by adding/changing the environment variables CS_VAR_LEFT_MARKER and CS_VAR_RIGHT_MARKER. For example, if you have CS_VAR_LEFT_MARKER set to $% and CS_VAR_RIGHT_MARKER set to %$, you would use $%MBRTEXT%$ rather than <:MBRTEXT>; etc.
These variable names, including the markers, are replaced at run time by the corresponding data value. Variable names are not case-sensitive.
|<:CURJOB>||Current job name|
|<:CURUSER>||Current user id|
|<:CURJOBNBR>||Current job number|
|<:FROMFILE>||Name of the database file specified on FROMFILE|
|<:FROMLIB>||Library specified on FROMFILE|
|<:FROMMBR>||Member name specified on FROMFILE|
|<:RCDFMT>||Name of the record format in the file specified on FROMFILE. Invalid if more than one record format is selected for processing.|
|<:FILETEXT>||File-level text of the file specified on FROMFILE|
|<:MBRTEXT>||Member-level text of the member specified on FROMFILE|
|<:RCDTEXT>||Record format-level text of the record format in the file specified on FROMFILE. Invalid if more than one record format is selected for processing.|
|<:QRYDFN>||Name of the Query/400 query object specified on the QRYDFN parameter|
|<:QRYDFNLIB>||Library name specified on the QRYDFN parameter|
|<:QMQRY>||Name of the QM Query object specified on the QMQRY parameter|
|<:QMQRYLIB>||Library name specified on the QMQRY parameter|
|<:STMFEXT>||Default file extension corresponding to the format being output (e.g. ‘.CSV’ when CSV being generated or ‘.XLS’ when an Excel file is being created.|
|<:TOFMT>||To-format. The format of the data being generated (corresponding to the TOFMT parameter of the CVTDBFSTMF command), .e.g. ‘*CSV’, ‘*XLS’|
|<:CURDATE>||The current date in the format of the current job (DATFMT attribute).|
|<:CURDATE*xxx>||The current date in the format indicated by *xxx, where *xxx is any one of: *YMD, *MDY, *DMY, *YYMD, *MDYY, *DMYY, *CYMD, *CMDY, *CDMY, *ISO, *EUR, *JIS, *JUL, *LONGJUL, *JOB or *SYSVAL.|
|<:CURDAY>||The current day of the month as a number 01-31.|
|<:CURMONTH>||The current month as a number 01-12.|
|<:CURYEAR>||The current year as a number 0001-9999|
|<:CURYEAR4>||The current year as a number 0001-9999|
|<:CURYEAR3>||The current year as a number c01-c99 where c is 0 for the 20th century and 1 for the 21st.|
|<:CURYEAR2>||The current year as a number 01-99.|
|<:CURTIME>||The current time in hhmmss format.|
|<:CURUSEREMAIL>||The email address of current user. The email address is the SMTP email address of the user from the system directory.|
|<:CURUSERNAME>||The name of the current user. The name is derived from the information held for the user in the system directory.|
|<:CURUSERHOME>||The home directory of the current user. The home directory is taken from the HOMEDIR attribute of the user profile.|
CVTDBFXL FROMFILE(QADSPOBJ) TOSTMF('<:fromlib>_<:fromfile>_<:frommbr>.xls') EMAIL(*YES) EMAILTO((<:CURUSEREMAIL>))
Here the CVTDBFXL command is being applied to a database file called QADSPOBJ. The name of the stream file to be generated will be derived from various the library name, file name and member name, e.g.: