Skip to Content

Defining database functions in AR System server

As an AR System administrator, you can use the AR System JDBC:Generic Db Functions form to define a database function on the AR System server. These functions are mainly used for reporting purposes.

In the AR System JDBC:Generic Db Functions form, you define the database functions by passing arguments to the function and by defining the return type of the function. You can pass up to 10 arguments to a function.

Related topics

Generating KCS custom reports

Performing-calculations-on-fields-in-a-report

Using-the-driver-program-from-the-command-line

Before you begin

You must run the database scripts required for using the AR System JDBC:Generic Db Functions form using the Java driver. These scripts are in the respective database folders located at ARSystem\artools\arjdbc. Before defining database functions through the AR System JDBC:Generic Db Functions form, make sure that the database scripts have run successfully by checking the following parameters in AR System Administration > AR System Administration Console > General > Centralized Configuration, from the Component Name list, select com.bmc.arsys.server.shared > * :

Parameter

Value

ARJdbc-Script-Execution-Status

Done

Use-Enum-Field-Recompute

T

If the Centralized Configuration parameter ARJdbc-Script-Execution-Status has the value Error and Use-Enum-Field-Recompute has the value F, then view the arcustomdbfunction.log file located at ARSystem\Arserver\Db for details. After viewing the log, run the database scripts again by issuing the following command via the Java driver.
This command runs both the enum functions and the DST date functions. The DST date functions include time zone information and DST offset adjustment:

PERFORM-CUSTOM-DB-FUNCTION-CHANGE

To execute only the DST date functions, run the command with the optional dstOnly parameter:

PERFORM-CUSTOM-DB-FUNCTION-CHANGE dstOnly

To verify whether the DST date functions have been executed, check the value of the DST-Timezone-Info-Populated CCS parameter. The parameter value is T if the DST date functions were executed, and F if they were not. For more information, see Configuration settings C-D. 

To execute commands using the Java driver

  1. From the command prompt, go to the C:\Program Files\BMC Software\ARSystem\Arserver\api\lib directory and execute the javadriver.bat command.
  2. Execute the log command with the required options.
    If the port is not default (0), then run the SSP command to set the server port and execute the VER command to verify the results of the command.
  3. Execute the proc command.
  4. Perform one of the following steps: 

    • Execute the PERFORM-CUSTOM-DB-FUNCTION-CHANGE command to run both enum and DST date functions.

    • Execute the PERFORM-CUSTOM-DB-FUNCTION-CHANGE dstOnly command to run only DST date functions.

  5. When prompted with Wait for process to complete(F), enter T

For more information about using the driver program, see Using-the-driver-program-from-the-command-line.

To define a new database function

Perform the following steps to define a function in AR System serverfor a particular database:

  1. Login to AR System.
  2. Select AR System Administrator > AR System JDBC:Generic Db Functions.
    ARJDBC Form-New.png
  3. On the AR System JDBC:Generic Db Functions form, click New request.

  4. Enter values for the following fields:

    Field name

    Description

    Function Name

    Name of the database function.
    Important: The function name is case-sensitive.

    Function Return Type

    Return type of the database function.

    Function Information

    Specific information about the database function.

    Function GUID

    A unique function identifier.

    Variable Argument

    Indicates whether the number of arguments required for the function are fixed or variable.
    A Yes value indicates that the number of arguments required for the function is not fixed. When you select Yes, all the argument data type fields are disabled.
    A No value indicates that the number of arguments required for the function is fixed.

    Function Database Type

    The database for which you are creating the function request.

    Minimum Function Argument Number

    The minimum number of arguments that you want to pass to the function.

    Arg1 Data Type

    Data type of argument one.

    Arg2 Data Type

    Data type of argument two.

    Arg3 Data Type

    Data type of argument three.

    Arg4 Data Type

    Data type of argument four.

    Arg5 Data Type

    Data type of argument five.

    Arg6 Data Type

    Data type of argument six.

    Arg7 Data Type

    Data type of argument seven.

    Arg8 Data Type

    Data type of argument eight.

    Arg9 Data Type

    Data type of argument nine.

    Arg10 Data Type

    Data type of argument ten.

    Submitter

    Name of the database function request submitter.

    Create Date

    Database function creation date.

    Last Modified by

    Name of the user who last modified the request.

    Modified Date

    Last modified date.

  5. Click Save.
    For troubleshooting issues related to defining functions in AR System server, see the API and SQL log files.

 

 

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*

BMC Helix Innovation Suite 25.4