Database Maintenance with Options

Objective: The AE database should be archived automatically with a workflow. A dialog should open in which the user can enter the specific settings.

Objects used: Job, Workflow, Script and Variable

Script elements used: ACTIVATE_UC_OBJECT, :BEGINREAD, GET_VAR, :PRINT, :PUT_VAR and :READ

Example

AE database maintenance is an integral part of AE system administration. Utilities are available which serve this purpose and which can also be used in batch mode. Creating a workflow containing the particular steps for database maintenance is very useful. The parameters (e.g. client) can be entered by the user through an input dialog.

Variable

First create a static variable with the following attributes:

This static variable will contain the client number and the values "Y" or "N" for each step in the maintaining procedure, depending on whether the user wants to carry out the particular step or not.

Script

Create a script in the next step which generates an input dialog with the script element :BEGINREAD. This input dialog receives the relevant information from the user:

Use the parameters for :BEGINREAD to specify that only numbers ranging from 0 to 9999 can be used for the client. The possible settings should be the values "Y" and "N". The selected options must be stored in the Variable object. You can also use them to automatically have the options used for the last maintenance procedure entered in the input dialog. Of course, the user can also specify different settings.

The script starts a workflow which carries out the actual maintaining procedure with the script function ACTIVATE_UC_OBJECT. Remember to query beforehand if the value "N" was selected for all options (archiving, reorganizing, unloading). In this case, the workflow would not start at all.

!Settings of the last maintaining procedure are to be retrieved.

:SET &default_value_m# = GET_VAR('VARA.DATABASE_MAINTENANCE','CLIENT')

:SET &default_value_a# = GET_VAR('VARA.DATABASE_MAINTENANCE','ARCHIVING')

:SET &default_value_r# = GET_VAR('VARA.DATABASE_MAINTENANCE','REORGANIZING')

:SET &default_value_e# = GET_VAR('VARA.DATABASE_MAINTENANCE','UNLOADING')

 

 

!A Dialog is to be displayed in which the user can specify the options for database maintenance

:BEGINREAD

:PRINT "MAINTAINING THE AE database"

:PRINT "--------------------------------------------------------------------------"

:READ &client#, "0-9999", 'client (0-9999)', &default_value_m#

:PRINT "--------------------------------------------------------------------------"

:PRINT ""

:PRINT "Archiving"

:READ &archiving#, "'Y','N'",'Shall the database be archived?', &default_value_a#

:PRINT ""

:PRINT "Reorganizing"

:READ &reorganizing#, "'Y','N'",'Shall the database be reorganized?', &default_value_r#

:PRINT ""

:PRINT "Unloading the DB"

:READ &unloading#, "'Y','N'",'Shall the database be unloaded?', &default_value_e#

:PRINT ""

:PRINT "--------------------------------------------------------------------------"

:ENDREAD

 

 

!If the user selects "No" for each option, database maintenance is not necessary.

:IF &archiving# = 'N'

:   IF &reorganizing# = 'N'

:      IF &unloading# = 'N'

:      SET &start# = 'N'

:      ENDIF

:   ENDIF

:ENDIF

 

:IF &start# = 'N'

:  BEGINREAD

:  PRINT 'Database maintenance not necessary.'

:  ENDREAD

:ELSE

:  PUT_VAR 'VARA.DATABASE_MAINTENANCE','CLIENT',&client#

:  PUT_VAR 'VARA.DATABASE_MAINTENANCE','ARCHIVING',&archiving#

:  PUT_VAR 'VARA.DATABASE_MAINTENANCE','REORGANIZING',&reorganizing#

:  PUT_VAR 'VARA.DATABASE_MAINTENANCE','UNLOADING',&unloading#

!The workflow for database maintenance is started.

:  SET &ret = ACTIVATE_UC_OBJECT('JOBP.DATABASE_MAINTENANCE')

:ENDIF

If the option Generate at runtime has been activated in the attributes of the script, the input dialog is not displayed! In this case, the default values are used. This also holds for all other executable objects.

Workflow and Jobs

Create a workflow which contains a job for each individual maintenance step. The scripts are all the same except for calling the utility. First, it is to be determined if the particular step is to be carried out and the client that is concerned. Then, the utility is called in batch mode or written in the activation report that this particular step is omitted.

JOBS.ARCHIVING

:SET &archiving# = GET_VAR('VARA.DATABASE_MAINTENANCE','ARCHIVING')
:SET &client# = GET_VAR('VARA.DATABASE_MAINTENANCE','CLIENT')

:IF &archiving# = 'Y' 
UCYBDBar -B -S&client
:ELSE
:   PRINT 'Archiving shall not take place.'
:ENDIF

JOBS.REORGANIZING

:SET &reorganizing# = GET_VAR('VARA.DATABASE_MAINTENANCE','REORGANIZING')
:SET &mclient# = GET_VAR('VARA.DATABASE_MAINTENANCE','CLIENT')

:IF &reorganizing# = 'Y' 
UCYBDBre -B -S&client#
:ELSE
:   PRINT 'Reorganization shall not take place.'
:ENDIF

JOBS.UNLOADING

:SET &unloading# = GET_VAR('VARA.DATABASE_MAINTENANCE','UNLOADING')

:IF &unloading# = 'Y' 
UCYBDBun -BREORG

:ELSE
:   PRINT 'Unloading of data records shall not take place.'
:ENDIF

 

Automic recommends executing an error check each time you call the utility if you use Windows jobs, and adjust the return code if necessary. The effect is that the job aborts if the execution of the utility fails. In doing so, you can quickly identify the error and react to it.

 

Insert the following two JCL lines after the program call:

@set retcode=%errorlevel%

@if NOT %ERRORLEVEL% == 0 goto :retcode