Callable Procedures
  
Callable Procedures
Introduction to Callable Procedures
Callable procedures and functions provide a means of interacting with the QAD Data Warehouse Designer metadata from within a stored procedure. In many situations the use of the WsWrkAudit, WsWrkError and WsWrkTask procedures are used to provide additional information on the results of a procedures execution. The QAD Data Warehouse Designer callable procedures are an application program interface (API) for the QAD metadata.
The WsParameterRead and WsParameterWrite functions can be used to coordinate processing and pass information between procedures.
The following callable procedures are available:
 
Name
Description
Ws_Api_Glossary (on page 814)
Allows adding of entries to the documentation glossary.
Ws_Connect_Replace (see "Ws_Api_Glossary" on page 814)
Allows copying of connection details.
Ws_Job_Clear_Archive (on page 823)
Removes the job archive logs.
Ws_Job_Clear_Logs (on page 828)
Removes and archives old job logs.
Ws_Job_Clear_Logs_By_Date (see "Ws_Job_Clear_Logs" on page 828)
Removes and archives old job logs by number of days.
Ws_Job_Create (on page 839)
Creates a new job based on an existing job to run immediately.
Ws_Job_CreateWait (on page 845)
Creates a new job based on an existing job to run at specified time.
Ws_Job_Dependency (on page 851)
Allows the caller to add or remove job dependencies
Ws_Job_Release (on page 857)
Allows the automated release of a held or waiting job.
Ws_Job_Restart (on page 862)
Allows the automated restarting of a failed job.
Ws_Job_Schedule (on page 867)
Allows the automated rescheduling of a hold or waiting job.
Ws_Job_Status (see "Ws_Job_Schedule" on page 867)
Returns the current status of a job.
Ws_Load_Change (on page 881)
Allows the changing of either the connection or the schema of a load table.
Ws_Maintain_Indexes (on page 886)
Drops and builds metadata defined indexes.
Ws_Version_Clear (on page 890)
Removes version entries from metadata.
WsParameterRead (on page 896)
Reads a parameter value.
WsParameterReadG (on page 900)
Reads a global parameter (i.e. table or source table name).
WsParameterReadJ (on page 904)
Reads job information and attributes.
WsParameterWrite (on page 907)
Updates or adds a new parameter value.
WsWrkAudit (on page 910)
Used to log information to the Audit log.
WsWrkAuditBulk (on page 914)
Allows a bulk write to the audit trail.
WsWrkError (on page 919)
Allows a bulk write to the detail/error log.
WsWrkErrorBulk (on page 923)
Updates the counters stored against a scheduled job task.
WsWrkTask (on page 927)
Used to log information to the detail/error log.
 
 
Terminology
Some relational databases provide for SQL user defined functions that are able to perform complex tasks. The QAD Data Warehouse Designer APIs exist as database procedures in some databases and database functions in others. In one case, both a procedure and function version of the same API exist in Microsoft SQL Server. To avoid confusion, this chapter refers to all APIs as procedures, even if they are functions. At the beginning of each section, a tables shows the form of the API in each supported relational database.
 
Common Parameters
All of the QAD Data Warehouse Designer APIs have input and output parameters.
The following input parameters are common to most of the QAD Data Warehouse Designer APIs, primarily for scheduler integration:
Parameter
Description
Sequence
Unique integer sequence number to assist in identifying messages. If being executed manually or externally to the scheduler, then any number may be entered. In Oracle, the number can be acquired from the ws-job-seq sequence. If under scheduler control, then the procedure will have been passed the sequence parameter.
Job Name
Maximum 64 character code that is the name of the job, when running under the scheduler. If being executed manually, any name may be entered. If under scheduler control, then the procedure will have been passed the job name parameter.
Task Name
Maximum 64 character code that is the name of the task, when running under the scheduler. If being executed manually, any name may be entered. If under scheduler control, then the procedure will have been passed the task name parameter.
Job Key
Integer key of the job, when running under the scheduler. If being executed manually, then this key should be set to zero (0). If under scheduler control, then the procedure will have been passed the job id parameter.
Task Key
Integer key of the task, when running under the scheduler. If being executed manually, then this key should be set to zero (0). If under scheduler control, then the procedure will have been passed the task id parameter.
In the sections that follow, each API using one or more of the parameters above includes the parameter name without descriptions.
 
Ws_Api_Glossary
This procedure allows for the manual inclusion of glossary elements. These glossary elements once included will appear in all subsequent documentation produced by QAD Data Warehouse Designer.