Class SystemProcedures

java.lang.Object
org.apache.derby.catalog.SystemProcedures

public class SystemProcedures extends Object
Some system built-in procedures, and help routines. Now used for network server. These procedures are built-in to the SYSIBM schema which match the DB2 SYSIBM procedures. Currently information on those can be found at url: ftp://ftp.software.ibm.com/ps/products/db2/info/vr8/pdf/letter/db2l2e80.pdf

Also used for builtin-routines, such as SYSFUN functions, when direct calls into Java libraries cannot be made.

  • Field Details

  • Constructor Details

    • SystemProcedures

      public SystemProcedures()
  • Method Details

    • SQLCAMESSAGE

      public static void SQLCAMESSAGE(int sqlcode, short errmcLen, String sqlerrmc, String sqlerrp, int errd0, int errd1, int errd2, int errd3, int errd4, int errd5, String warn, String sqlState, String file, String localeStr, String[] msg, int[] rc)
      Method used by Derby Network Server to get localized message (original call from jcc.
      Parameters:
      sqlcode - sqlcode, not used.
      errmcLen - sqlerrmc length
      sqlerrmc - sql error message tokens, variable part of error message (ie., arguments) plus messageId, separated by separator.
      sqlerrp - not used
      errd0 - not used
      errd1 - not used
      errd2 - not used
      errd3 - not used
      errd4 - not used
      errd5 - not used
      warn - not used
      sqlState - 5-char sql state
      file - not used
      localeStr - client locale in string
      msg - OUTPUT parameter, localized error message
      rc - OUTPUT parameter, return code -- 0 for success
    • getDefaultConn

      private static Connection getDefaultConn() throws SQLException
      Get the default or nested connection corresponding to the URL jdbc:default:connection. We do not use DriverManager here as it is not supported in JSR 169. IN addition we need to perform more checks for null drivers or the driver returing null from connect as that logic is in DriverManager.
      Returns:
      The nested connection
      Throws:
      SQLException - Not running in a SQL statement
    • getDMD

      private static DatabaseMetaData getDMD() throws SQLException
      Get the DatabaseMetaData for the current connection for use in mapping the jcc SYSIBM.* calls to the Derby DatabaseMetaData methods
      Returns:
      The DatabaseMetaData object of the current connection
      Throws:
      SQLException
    • SQLPROCEDURES

      public static void SQLPROCEDURES(String catalogName, String schemaName, String procName, String options, ResultSet[] rs) throws SQLException
      Map SQLProcedures to EmbedDatabaseMetaData.getProcedures
      Parameters:
      catalogName - SYSIBM.SQLProcedures CatalogName varchar(128),
      schemaName - SYSIBM.SQLProcedures SchemaName varchar(128),
      procName - SYSIBM.SQLProcedures ProcName varchar(128),
      options - SYSIBM.SQLProcedures Options varchar(4000))
      rs - output parameter, the resultset object containing the result of getProcedures If options contains the string 'DATATYPE='ODBC'', call the ODBC version of this procedure.
      Throws:
      SQLException
    • SQLFUNCTIONS

      public static void SQLFUNCTIONS(String catalogName, String schemaName, String funcName, String options, ResultSet[] rs) throws SQLException
      Map SQLFunctions to EmbedDatabaseMetaData.getFunctions
      Parameters:
      catalogName - SYSIBM.SQLFunctions CatalogName varchar(128),
      schemaName - SYSIBM.SQLFunctions SchemaName varchar(128),
      funcName - SYSIBM.SQLFunctions ProcName varchar(128),
      options - SYSIBM.SQLFunctions Options varchar(4000)) (not used)
      rs - output parameter, the resultset object containing the result of getFunctions
      Throws:
      SQLException
    • SQLTABLES

      public static void SQLTABLES(String catalogName, String schemaName, String tableName, String tableType, String options, ResultSet[] rs) throws SQLException
      Map SQLTables to EmbedDatabaseMetaData.getSchemas, getCatalogs, getTableTypes and getTables, and return the result of the DatabaseMetaData calls.

      JCC and DNC overload this method:

      • If options contains the string 'GETSCHEMAS=1', call getSchemas()
      • If options contains the string 'GETSCHEMAS=2', call getSchemas(String, String)
      • If options contains the string 'GETCATALOGS=1', call getCatalogs()
      • If options contains the string 'GETTABLETYPES=1', call getTableTypes()
      • otherwise, call getTables()
      Parameters:
      catalogName - SYSIBM.SQLTables CatalogName varchar(128),
      schemaName - SYSIBM.SQLTables SchemaName varchar(128),
      tableName - SYSIBM.SQLTables TableName varchar(128),
      tableType - SYSIBM.SQLTables TableType varchar(4000))
      options - SYSIBM.SQLTables Options varchar(4000))
      rs - output parameter, the resultset object
      Throws:
      SQLException
    • SQLFOREIGNKEYS

      public static void SQLFOREIGNKEYS(String pkCatalogName, String pkSchemaName, String pkTableName, String fkCatalogName, String fkSchemaName, String fkTableName, String options, ResultSet[] rs) throws SQLException
      Map SQLForeignKeys to EmbedDatabaseMetaData.getImportedKeys, getExportedKeys, and getCrossReference
      Parameters:
      pkCatalogName - SYSIBM.SQLForeignKeys PKCatalogName varchar(128),
      pkSchemaName - SYSIBM.SQLForeignKeys PKSchemaName varchar(128),
      pkTableName - SYSIBM.SQLForeignKeys PKTableName varchar(128),
      fkCatalogName - SYSIBM.SQLForeignKeys FKCatalogName varchar(128),
      fkSchemaName - SYSIBM.SQLForeignKeys FKSchemaName varchar(128),
      fkTableName - SYSIBM.SQLForeignKeys FKTableName varchar(128),
      options - SYSIBM.SQLForeignKeys Options varchar(4000))
      rs - output parameter, the resultset object containing the result of the DatabaseMetaData calls JCC overloads this method: If options contains the string 'EXPORTEDKEY=1', call getImportedKeys If options contains the string 'IMPORTEDKEY=1', call getExportedKeys otherwise, call getCrossReference
      Throws:
      SQLException
    • getOption

      private static String getOption(String pattern, String options)
      Helper for SQLForeignKeys and SQLTables
      Parameters:
      pattern - String containing the option to search for
      options - String containing the options to search through
      Returns:
      option String containing the value for a given option
    • SQLPROCEDURECOLS

      public static void SQLPROCEDURECOLS(String catalogName, String schemaName, String procName, String paramName, String options, ResultSet[] rs) throws SQLException
      Map SQLProcedureCols to EmbedDatabaseMetaData.getProcedureColumns
      Parameters:
      catalogName - SYSIBM.SQLProcedureCols CatalogName varchar(128),
      schemaName - SYSIBM.SQLProcedureCols SchemaName varchar(128),
      procName - SYSIBM.SQLProcedureCols ProcName varchar(128),
      paramName - SYSIBM.SQLProcedureCols ParamName varchar(128),
      options - SYSIBM.SQLProcedureCols Options varchar(4000))
      rs - output parameter, the resultset object containing the result of getProcedureColumns If options contains the string 'DATATYPE='ODBC'', call the ODBC version of this procedure.
      Throws:
      SQLException
    • SQLFUNCTIONPARAMS

      public static void SQLFUNCTIONPARAMS(String catalogName, String schemaName, String funcName, String paramName, String options, ResultSet[] rs) throws SQLException
      Map SQLFunctionParameters to EmbedDatabaseMetaData.getFunctionColumns()
      Parameters:
      catalogName - SYSIBM.SQLFunctionParameters CatalogName varchar(128),
      schemaName - SYSIBM.SQLFunctionParameters SchemaName varchar(128),
      funcName - SYSIBM.SQLFunctionParameters FuncName varchar(128),
      paramName - SYSIBM.SQLFunctionParameters ParamName varchar(128),
      options - SYSIBM.SQLFunctionParameters Options varchar(4000))
      rs - output parameter, the resultset object containing the result of getFunctionColumns().
      Throws:
      SQLException
    • SQLCOLUMNS

      public static void SQLCOLUMNS(String catalogName, String schemaName, String tableName, String columnName, String options, ResultSet[] rs) throws SQLException
      Map SQLColumns to EmbedDatabaseMetaData.getColumns
      Parameters:
      catalogName - SYSIBM.SQLColumns CatalogName varchar(128),
      schemaName - SYSIBM.SQLColumns SchemaName varchar(128),
      tableName - SYSIBM.SQLColumns TableName varchar(128),
      columnName - SYSIBM.SQLColumns ColumnName varchar(128),
      options - SYSIBM.SQLColumns Options varchar(4000)) If options contains the string 'DATATYPE='ODBC'', call the ODBC version of this procedure.
      rs - output parameter, the resultset object containing the result of getProcedures
      Throws:
      SQLException
    • SQLCOLPRIVILEGES

      public static void SQLCOLPRIVILEGES(String catalogName, String schemaName, String tableName, String columnName, String options, ResultSet[] rs) throws SQLException
      Map SQLColPrivileges to EmbedDatabaseMetaData.getColumnPrivileges
      Parameters:
      catalogName - SYSIBM.SQLColPrivileges CatalogName varchar(128),
      schemaName - SYSIBM.SQLColPrivileges SchemaName varchar(128),
      tableName - SYSIBM.SQLColPrivileges ProcName varchar(128),
      columnName - SYSIBM.SQLColPrivileges ColumnName varchar(128),
      options - SYSIBM.SQLColPrivileges Options varchar(4000))
      rs - output parameter, the resultset object containing the result of getColumnPrivileges
      Throws:
      SQLException
    • SQLTABLEPRIVILEGES

      public static void SQLTABLEPRIVILEGES(String catalogName, String schemaName, String tableName, String options, ResultSet[] rs) throws SQLException
      Map SQLTablePrivileges to EmbedDatabaseMetaData.getTablePrivileges
      Parameters:
      catalogName - SYSIBM.SQLTablePrivileges CatalogName varchar(128),
      schemaName - SYSIBM.SQLTablePrivileges SchemaName varchar(128),
      tableName - SYSIBM.SQLTablePrivileges ProcName varchar(128),
      options - SYSIBM.SQLTablePrivileges Options varchar(4000))
      rs - output parameter, the resultset object containing the result of getTablePrivileges
      Throws:
      SQLException
    • SQLPRIMARYKEYS

      public static void SQLPRIMARYKEYS(String catalogName, String schemaName, String tableName, String options, ResultSet[] rs) throws SQLException
      Map SQLPrimaryKeys to EmbedDatabaseMetaData.getPrimaryKeys
      Parameters:
      catalogName - SYSIBM.SQLPrimaryKeys CatalogName varchar(128),
      schemaName - SYSIBM.SQLPrimaryKeys SchemaName varchar(128),
      tableName - SYSIBM.SQLPrimaryKeys TableName varchar(128),
      options - SYSIBM.SQLPrimaryKeys Options varchar(4000)) If options contains the string 'DATATYPE='ODBC'', call the ODBC version of this procedure.
      rs - output parameter, the resultset object containing the result of getPrimaryKeys
      Throws:
      SQLException
    • SQLGETTYPEINFO

      public static void SQLGETTYPEINFO(short dataType, String options, ResultSet[] rs) throws SQLException
      Map SQLGetTypeInfo to EmbedDatabaseMetaData.getTypeInfo
      Parameters:
      dataType - SYSIBM.SQLGetTypeInfo DataType smallint,
      options - SYSIBM.SQLGetTypeInfo Options varchar(4000)) If options contains the string 'DATATYPE='ODBC'', call the ODBC version of this procedure.
      rs - output parameter, the resultset object containing the result of getTypeInfo
      Throws:
      SQLException
    • SQLSTATISTICS

      public static void SQLSTATISTICS(String catalogName, String schemaName, String tableName, short unique, short approximate, String options, ResultSet[] rs) throws SQLException
      Map SQLStatistics to EmbedDatabaseMetaData.getIndexInfo
      Parameters:
      catalogName - SYSIBM.SQLStatistics CatalogName varchar(128),
      schemaName - SYSIBM.SQLStatistics SchemaName varchar(128),
      tableName - SYSIBM.SQLStatistics TableName varchar(128),
      unique - SYSIBM.SQLStatistics Unique smallint; 0=SQL_INDEX_UNIQUE(0); 1=SQL_INDEX_ALL(1),
      approximate - SYSIBM.SQLStatistics Approximate smallint; 1=true; 0=false,
      options - SYSIBM.SQLStatistics Options varchar(4000)) If options contains the string 'DATATYPE='ODBC'', call the ODBC version of this procedure.
      rs - output parameter, the resultset object containing the result of getIndexInfo
      Throws:
      SQLException
    • SQLSPECIALCOLUMNS

      public static void SQLSPECIALCOLUMNS(short colType, String catalogName, String schemaName, String tableName, short scope, short nullable, String options, ResultSet[] rs) throws SQLException
      Map SQLSpecialColumns to EmbedDatabaseMetaData.getBestRowIdentifier and getVersionColumns
      Parameters:
      colType - SYSIBM.SQLSpecialColumns ColType smallint, where 1 means getBestRowIdentifier and 2 getVersionColumns was called.
      catalogName - SYSIBM.SQLSpecialColumns CatalogName varchar(128),
      schemaName - SYSIBM.SQLSpecialColumns SchemaName varchar(128),
      tableName - SYSIBM.SQLSpecialColumns TableName varchar(128),
      scope - SYSIBM.SQLSpecialColumns Scope smallint,
      nullable - SYSIBM.SQLSpecialColumns Nullable smallint; 0=false, 1=true,
      options - SYSIBM.SQLSpecialColumns Options varchar(4000)) If options contains the string 'DATATYPE='ODBC'', call the ODBC version of this procedure.
      rs - output parameter, the resultset object containing the result of the DatabaseMetaData call
      Throws:
      SQLException
    • SQLUDTS

      public static void SQLUDTS(String catalogName, String schemaPattern, String typeNamePattern, String udtTypes, String options, ResultSet[] rs) throws SQLException
      Map SQLUDTS to EmbedDatabaseMetaData.getUDTs
      Parameters:
      catalogName - SYSIBM.SQLUDTS CatalogName varchar(128),
      schemaPattern - SYSIBM.SQLUDTS Schema_Name_Pattern varchar(128),
      typeNamePattern - SYSIBM.SQLUDTS Type_Name_Pattern varchar(128),
      udtTypes - SYSIBM.SQLUDTS UDTTypes varchar(128),
      options - SYSIBM.SQLUDTS Options varchar(4000))
      rs - output parameter, the resultset object containing the result of getUDTs, which will be empty
      Throws:
      SQLException
    • METADATA

      public static void METADATA(ResultSet[] rs) throws SQLException
      Throws:
      SQLException
    • isForODBC

      private static boolean isForODBC(String options)
      Helper for ODBC metadata calls.
      Parameters:
      options - String containig the options to search through.
      Returns:
      True if options contain ODBC indicator; false otherwise.
    • SYSCS_SET_DATABASE_PROPERTY

      public static void SYSCS_SET_DATABASE_PROPERTY(String key, String value) throws SQLException
      Set/delete the value of a property of the database in current connection.

      Will be called as SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY.

      Parameters:
      key - The property key.
      value - The new value, if null the property is deleted.
      Throws:
      SQLException - if a database error occurs
    • setDatabaseProperty

      private static void setDatabaseProperty(String key, String value, Securable authorizationCheck) throws SQLException
      Throws:
      SQLException
    • SYSCS_GET_DATABASE_PROPERTY

      public static String SYSCS_GET_DATABASE_PROPERTY(String key) throws SQLException
      Get the value of a property of the database in current connection.

      Will be called as SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY.

      Parameters:
      key - The property key.
      Throws:
      SQLException - if a database error occurs
    • getProperty

      private static String getProperty(String key, Securable authorizationCheck) throws SQLException
      This method exists so that we can get a property value without performing authorization checks.
      Throws:
      SQLException
    • SYSCS_UPDATE_STATISTICS

      public static void SYSCS_UPDATE_STATISTICS(String schemaname, String tablename, String indexname) throws SQLException
      Update the statistics for 1)all the indexes or 2)a specific index on a table.

      Calls either "alter table tablename all update statistics " sql or "alter table tablename update statistics indexname" sql This routine will be called when an application calls: SYSCS_UTIL.SYSCS_UPDATE_STATISTICS

      Parameters:
      schemaname - schema name of the table/index(es) whose statistics will be updated. null will mean use the current schema to resolve the table name. Empty string for schema name will raise an exception.
      tablename - table name of the index(es) whose statistics will be updated. A null value or an empty string will throw table not found exception. Must be non-null.
      indexname - If null, then update the statistics for all the indexes for the given table name. If not null and not empty string, then the user wants to update the statistics for only the give index name. Empty string for index name will raise an exception.
      Throws:
      SQLException
    • SYSCS_DROP_STATISTICS

      public static void SYSCS_DROP_STATISTICS(String schemaname, String tablename, String indexname) throws SQLException
      Drop the statistics for 1)all the indexes or 2)a specific index on a table.
      Parameters:
      schemaname - schema name of the table/index(es) whose statistics will be dropped. null will mean use the current schema to resolve the table name. Empty string for schema name will raise an exception.
      tablename - table name of the index(es) whose statistics will be dropped. A null value or an empty string will throw table not found exception. Must be non-null.
      indexname - If null, then drop the statistics for all the indexes for the given table name. If not null and not empty string, then the user wants to drop the statistics for only the give index name. Empty string for index name will raise an exception.
      Throws:
      SQLException
    • basicSchemaTableValidation

      private static String basicSchemaTableValidation(String schemaname, String tablename) throws SQLException
      Do following checks a)Schema name can't be empty string b)If schema name is null, then we use current schema c)Table name can't be null or empty string
      Parameters:
      schemaname - If schema name is null, then we will use the current schema to resolve the table name. Empty string for schema name will raise an exception.
      tablename - If table name is null or an empty string, we will throw table not found exception.
      Returns:
      schemaname.tablename or tablename
      Throws:
      SQLException - a)if schema name is empty string b)if table name is empty string c)if table name is null
    • SYSCS_COMPRESS_TABLE

      public static void SYSCS_COMPRESS_TABLE(String schemaname, String tablename, short sequential) throws SQLException
      Compress the table.

      Calls the "alter table compress {sequential}" sql. This syntax is not db2 compatible so it mapped by a system routine. This routine will be called when an application calls: SYSCS_UTIL.SYSCS_COMPRESS_TABLE

      Parameters:
      schemaname - schema name of the table to compress. null will mean use the current schema to resolve the table name. Empty string for schema name will raise an exception.
      tablename - table name of the table to compress. A null value or an empty string will throw table not found exception. Must be non-null.
      sequential - if non-zero then rebuild indexes sequentially, if 0 then rebuild all indexes in parallel.
      Throws:
      SQLException - if a database error occurs
    • SYSCS_FREEZE_DATABASE

      public static void SYSCS_FREEZE_DATABASE() throws SQLException
      Freeze the database.

      Call internal routine to freeze the database so that a backup can be made.

      Throws:
      SQLException - if a database error occurs
    • SYSCS_UNFREEZE_DATABASE

      public static void SYSCS_UNFREEZE_DATABASE() throws SQLException
      Unfreeze the database.

      Call internal routine to unfreeze the database, which was "freezed" by calling SYSCS_FREEZE_DATABASE(). can be made.

      Throws:
      SQLException - if a database error occurs
    • SYSCS_CHECKPOINT_DATABASE

      public static void SYSCS_CHECKPOINT_DATABASE() throws SQLException
      Throws:
      SQLException
    • SYSCS_BACKUP_DATABASE

      public static void SYSCS_BACKUP_DATABASE(String backupDir) throws SQLException
      Backup the database to a backup directory. This procedure will throw error, if there are any unlogged operation executed in the same transaction backup is started. If there any unlogged operations in progess in other transaction, it will wait until those transactions are completed before starting the backup. Examples of unlogged operations include: create index and bulk insert. Note that once the backup begins these operations will not block, instead they are automatically converted into logged operations.
      Parameters:
      backupDir - the name of the directory where the backup should be stored. This directory will be created if it does not exist.
      Throws:
      SQLException - if a database error occurs
    • SYSCS_BACKUP_DATABASE_NOWAIT

      public static void SYSCS_BACKUP_DATABASE_NOWAIT(String backupDir) throws SQLException
      Backup the database to a backup directory. This procedure will throw error, if there are any uncommitted unlogged operation before stating the backup. It will not wait for the unlogged operations to complete. Examples of unlogged operations include: create index and bulk insert. Note that once the backup begins these operations will not block, instead they are automatically converted into logged operations.
      Parameters:
      backupDir - the name of the directory where the backup should be stored. This directory will be created if it does not exist.
      Throws:
      SQLException - if a database error occurs
    • SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE

      public static void SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE(String backupDir, short deleteOnlineArchivedLogFiles) throws SQLException
      Backup the database to a backup directory and enable the log archive mode that will keep the archived log files required for roll-forward from this version of the backup. This procedure will throw error if there are any unlogged operation executed in the same transaction backup is started. If there any unlogged operations in progess in other transaction, it will wait until those transactions are completed before starting the backup. Examples of unlogged operations include: create index and bulk insert. Note that once the backup begins these operations will not block, instead they are automatically converted into logged operations.
      Parameters:
      backupDir - the name of the directory where the backup should be stored. This directory will be created if not it does not exist.
      deleteOnlineArchivedLogFiles - If non-zero deletes online archived log files that exist before this backup, delete will occur only after the backup is complete.
      Throws:
      SQLException - if a database error occurs
    • SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE_NOWAIT

      public static void SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE_NOWAIT(String backupDir, short deleteOnlineArchivedLogFiles) throws SQLException
      Backup the database to a backup directory and enable the log archive mode that will keep the archived log files required for roll-forward from this version backup. This procedure will throw error, if there are any uncommitted unlogged operation before stating the backup. It will not wait for the unlogged operations to complete. Examples of unlogged operations include: create index and bulk insert. Note that once the backup begins these operations will not block, instead they are automatically converted into logged operations.
      Parameters:
      backupDir - the name of the directory where the backup should be stored. This directory will be created if not it does not exist.
      deleteOnlineArchivedLogFiles - If non-zero deletes online archived log files that exist before this backup, delete will occur only after the backup is complete.
      Throws:
      SQLException - if a database error occurs
    • SYSCS_DISABLE_LOG_ARCHIVE_MODE

      public static void SYSCS_DISABLE_LOG_ARCHIVE_MODE(short deleteOnlineArchivedLogFiles) throws SQLException
      Disables the log archival process, i.e No old log files will be kept around for a roll-forward recovery.
      Parameters:
      deleteOnlineArchivedLogFiles - If non-zero deletes all the online archived log files that exist before this call immediately.
      Throws:
      SQLException - if a database error occurs
    • SYSCS_SET_RUNTIMESTATISTICS

      public static void SYSCS_SET_RUNTIMESTATISTICS(short enable) throws SQLException
      Throws:
      SQLException
    • SYSCS_SET_STATISTICS_TIMING

      public static void SYSCS_SET_STATISTICS_TIMING(short enable) throws SQLException
      Throws:
      SQLException
    • SYSCS_CHECK_TABLE

      public static int SYSCS_CHECK_TABLE(String schema, String tablename) throws SQLException
      Throws:
      SQLException
    • SYSCS_INPLACE_COMPRESS_TABLE

      public static void SYSCS_INPLACE_COMPRESS_TABLE(String schema, String tablename, short purgeRows, short defragmentRows, short truncateEnd) throws SQLException
      Implementation of SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE().

      Code which implements the following system procedure: void SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE( IN SCHEMANAME VARCHAR(128), IN TABLENAME VARCHAR(128), IN PURGE_ROWS SMALLINT, IN DEFRAGMENT_ROWS SMALLINT, IN TRUNCATE_END SMALLINT)

      Use the SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE system procedure to reclaim unused, allocated space in a table and its indexes. Typically, unused allocated space exists when a large amount of data is deleted from a table, and there have not been subsequent inserts to use the space freed by the deletes. By default, Derby does not return unused space to the operating system. For example, once a page has been allocated to a table or index, it is not automatically returned to the operating system until the table or index is destroyed. SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE allows you to return unused space to the operating system.

      This system procedure can be used to force 3 levels of in place compression of a SQL table: PURGE_ROWS, DEFRAGMENT_ROWS, TRUNCATE_END. Unlike SYSCS_UTIL.SYSCS_COMPRESS_TABLE() all work is done in place in the existing table/index.

      Syntax: SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE( IN SCHEMANAME VARCHAR(128), IN TABLENAME VARCHAR(128), IN PURGE_ROWS SMALLINT, IN DEFRAGMENT_ROWS SMALLINT, IN TRUNCATE_END SMALLINT)

      SCHEMANAME: An input argument of type VARCHAR(128) that specifies the schema of the table. Passing a null will result in an error.

      TABLENAME: An input argument of type VARCHAR(128) that specifies the table name of the table. The string must exactly match the case of the table name, and the argument of "Fred" will be passed to SQL as the delimited identifier 'Fred'. Passing a null will result in an error.

      PURGE_ROWS: If PURGE_ROWS is set to non-zero then a single pass is made through the table which will purge committed deleted rows from the table. This space is then available for future inserted rows, but remains allocated to the table. As this option scans every page of the table, it's performance is linearly related to the size of the table.

      DEFRAGMENT_ROWS: If DEFRAGMENT_ROWS is set to non-zero then a single defragment pass is made which will move existing rows from the end of the table towards the front of the table. The goal of the defragment run is to empty a set of pages at the end of the table which can then be returned to the OS by the TRUNCATE_END option. It is recommended to only run DEFRAGMENT_ROWS, if also specifying the TRUNCATE_END option. This option scans the whole table and needs to update index entries for every base table row move, and thus execution time is linearly related to the size of the table.

      TRUNCATE_END: If TRUNCATE_END is set to non-zero then all contiguous pages at the end of the table will be returned to the OS. Running the PURGE_ROWS and/or DEFRAGMENT_ROWS passes options may increase the number of pages affected. This option itself does no scans of the table, so performs on the order of a few system calls.

      SQL example: To compress a table called CUSTOMER in a schema called US, using all available compress options: call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('US', 'CUSTOMER', 1, 1, 1); To quickly just return the empty free space at the end of the same table, this option will run much quicker than running all phases but will likely return much less space: call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('US', 'CUSTOMER', 0, 0, 1); Java example: To compress a table called CUSTOMER in a schema called US, using all available compress options: CallableStatement cs = conn.prepareCall ("CALL SYSCS_UTIL.SYSCS_COMPRESS_TABLE(?, ?, ?, ?, ?)"); cs.setString(1, "US"); cs.setString(2, "CUSTOMER"); cs.setShort(3, (short) 1); cs.setShort(4, (short) 1); cs.setShort(5, (short) 1); cs.execute(); To quickly just return the empty free space at the end of the same table, this option will run much quicker than running all phases but will likely return much less space: CallableStatement cs = conn.prepareCall ("CALL SYSCS_UTIL.SYSCS_COMPRESS_TABLE(?, ?, ?, ?, ?)"); cs.setString(1, "US"); cs.setString(2, "CUSTOMER"); cs.setShort(3, (short) 0); cs.setShort(4, (short) 0); cs.setShort(5, (short) 1); cs.execute();

      It is recommended that the SYSCS_UTIL.SYSCS_COMPRESS_TABLE procedure is issued in auto-commit mode. Note: This procedure acquires an exclusive table lock on the table being compressed. All statement plans dependent on the table or its indexes are invalidated. For information on identifying unused space, see the Derby Server and Administration Guide. TODO LIST: o defragment requires table level lock in nested user transaction, which will conflict with user lock on same table in user transaction.

      Throws:
      SQLException
    • SYSCS_GET_RUNTIMESTATISTICS

      public static String SYSCS_GET_RUNTIMESTATISTICS() throws SQLException
      Throws:
      SQLException
    • INSTALL_JAR

      public static void INSTALL_JAR(String url, String jar, int deploy) throws SQLException
      Install a jar file in the database. SQLJ.INSTALL_JAR
      Parameters:
      url - URL of the jar file to be installed in the database.
      jar - SQL name jar will be installed as.
      deploy - Ignored.
      Throws:
      SQLException - Error installing jar file.
    • REPLACE_JAR

      public static void REPLACE_JAR(String url, String jar) throws SQLException
      Replace a jar file in the database. SQLJ.REPLACE_JAR
      Parameters:
      url - URL of the jar file to be installed in the database.
      jar - SQL name of jar to be replaced.
      Throws:
      SQLException - Error replacing jar file.
    • REMOVE_JAR

      public static void REMOVE_JAR(String jar, int undeploy) throws SQLException
      Remove a jar file from the database.
      Parameters:
      jar - SQL name of jar to be replaced.
      undeploy - Ignored.
      Throws:
      SQLException - Error removing jar file.
    • checkJarSQLName

      private static void checkJarSQLName(String sqlName) throws StandardException
      Throws:
      StandardException
    • SYSCS_EXPORT_TABLE

      public static void SYSCS_EXPORT_TABLE(String schemaName, String tableName, String fileName, String columnDelimiter, String characterDelimiter, String codeset) throws SQLException
      Export data from a table to given file.

      Will be called by system procedure: SYSCS_EXPORT_TABLE(IN SCHEMANAME VARCHAR(128), IN TABLENAME VARCHAR(128), IN FILENAME VARCHAR(32672) , IN COLUMNDELIMITER CHAR(1), IN CHARACTERDELIMITER CHAR(1) , IN CODESET VARCHAR(128))

      Throws:
      SQLException - if a database error occurs
    • SYSCS_EXPORT_TABLE_LOBS_TO_EXTFILE

      public static void SYSCS_EXPORT_TABLE_LOBS_TO_EXTFILE(String schemaName, String tableName, String fileName, String columnDelimiter, String characterDelimiter, String codeset, String lobsFileName) throws SQLException
      Export data from a table to given files. Large objects are exported to an external file and the reference to it is written in the main export file.

      Will be called by system procedure: SYSCS_EXPORT_TABLE_LOBS_TO_EXTFILE(IN SCHEMANAME VARCHAR(128), IN TABLENAME VARCHAR(128), IN FILENAME VARCHAR(32672) , IN COLUMNDELIMITER CHAR(1), IN CHARACTERDELIMITER CHAR(1) , IN CODESET VARCHAR(128), IN LOBSFILENAME VARCHAR(32672))

      Throws:
      SQLException - if a database error occurs
    • SYSCS_EXPORT_QUERY

      public static void SYSCS_EXPORT_QUERY(String selectStatement, String fileName, String columnDelimiter, String characterDelimiter, String codeset) throws SQLException
      Export data from a select statement to given file.

      Will be called as SYSCS_EXPORT_QUERY(IN SELECTSTATEMENT VARCHAR(32672), IN FILENAME VARCHAR(32672) , IN COLUMNDELIMITER CHAR(1), IN CHARACTERDELIMITER CHAR(1) , IN CODESET VARCHAR(128))

      Throws:
      SQLException - if a database error occurs
    • SYSCS_EXPORT_QUERY_LOBS_TO_EXTFILE

      public static void SYSCS_EXPORT_QUERY_LOBS_TO_EXTFILE(String selectStatement, String fileName, String columnDelimiter, String characterDelimiter, String codeset, String lobsFileName) throws SQLException
      Export data from a select statement to given file. Large objects are exported to an external file and the reference to it is written in the main export file.

      Will be called as SYSCS_EXPORT_QUERY_LOBS_TO_EXTFILE(IN SELECTSTATEMENT VARCHAR(32672), IN FILENAME VARCHAR(32672) , IN COLUMNDELIMITER CHAR(1), IN CHARACTERDELIMITER CHAR(1) , IN CODESET VARCHAR(128), IN LOBSFILENAME VARCHAR(32672))

      Throws:
      SQLException - if a database error occurs
    • SYSCS_IMPORT_TABLE

      public static void SYSCS_IMPORT_TABLE(String schemaName, String tableName, String fileName, String columnDelimiter, String characterDelimiter, String codeset, short replace) throws SQLException
      Import data from a given file to a table.

      Will be called by system procedure as SYSCS_IMPORT_TABLE(IN SCHEMANAME VARCHAR(128), IN TABLENAME VARCHAR(128), IN FILENAME VARCHAR(32672) , IN COLUMNDELIMITER CHAR(1), IN CHARACTERDELIMITER CHAR(1) , IN CODESET VARCHAR(128), IN REPLACE SMALLINT)

      Throws:
      SQLException - if a database error occurs
    • SYSCS_IMPORT_TABLE_BULK

      public static void SYSCS_IMPORT_TABLE_BULK(String schemaName, String tableName, String fileName, String columnDelimiter, String characterDelimiter, String codeset, short replace, short skip) throws SQLException
      Throws:
      SQLException
    • rollBackAndThrowSQLException

      private static void rollBackAndThrowSQLException(Connection conn, SQLException se) throws SQLException
      issue a rollback when SQLException se occurs. If SQLException ouccurs when rollback, the new SQLException will be added into the chain of se.
      Throws:
      SQLException
    • SYSCS_IMPORT_TABLE_LOBS_FROM_EXTFILE

      public static void SYSCS_IMPORT_TABLE_LOBS_FROM_EXTFILE(String schemaName, String tableName, String fileName, String columnDelimiter, String characterDelimiter, String codeset, short replace) throws SQLException
      Import data from a given file to a table. Data for large object columns is in an external file, the reference to it is in the main input file. Read the lob data from the external file using the lob location info in the main import file.

      Will be called by system procedure as SYSCS_IMPORT_TABLE_LOBS_FROM_EXTFILE(IN SCHEMANAME VARCHAR(128), IN TABLENAME VARCHAR(128), IN FILENAME VARCHAR(32672) , IN COLUMNDELIMITER CHAR(1), IN CHARACTERDELIMITER CHAR(1) , IN CODESET VARCHAR(128), IN REPLACE SMALLINT)

      Throws:
      SQLException - if a database error occurs
    • SYSCS_IMPORT_DATA

      public static void SYSCS_IMPORT_DATA(String schemaName, String tableName, String insertColumnList, String columnIndexes, String fileName, String columnDelimiter, String characterDelimiter, String codeset, short replace) throws SQLException
      Import data from a given file into the specified table columns from the specified columns in the file.

      Will be called as SYSCS_IMPORT_DATA (IN SCHEMANAME VARCHAR(128), IN TABLENAME VARCHAR(128), IN INSERTCOLUMNLIST VARCHAR(32672), IN COLUMNINDEXES VARCHAR(32672), IN FILENAME VARCHAR(32672), IN COLUMNDELIMITER CHAR(1), IN CHARACTERDELIMITER CHAR(1), IN CODESET VARCHAR(128), IN REPLACE SMALLINT)

      Throws:
      SQLException - if a database error occurs
    • SYSCS_IMPORT_DATA_BULK

      public static void SYSCS_IMPORT_DATA_BULK(String schemaName, String tableName, String insertColumnList, String columnIndexes, String fileName, String columnDelimiter, String characterDelimiter, String codeset, short replace, short skip) throws SQLException
      Import data from a given file into the specified table columns skipping header lines from the specified columns in the file.

      Will be called as SYSCS_IMPORT_DATA_BULK (IN SCHEMANAME VARCHAR(128), IN TABLENAME VARCHAR(128), IN INSERTCOLUMNLIST VARCHAR(32672), IN COLUMNINDEXES VARCHAR(32672), IN FILENAME VARCHAR(32672), IN COLUMNDELIMITER CHAR(1), IN CHARACTERDELIMITER CHAR(1), IN CODESET VARCHAR(128), IN REPLACE SMALLINT, IN SKIP SMALLINT)

      Throws:
      SQLException - if a database error occurs
    • SYSCS_IMPORT_DATA_LOBS_FROM_EXTFILE

      public static void SYSCS_IMPORT_DATA_LOBS_FROM_EXTFILE(String schemaName, String tableName, String insertColumnList, String columnIndexes, String fileName, String columnDelimiter, String characterDelimiter, String codeset, short replace) throws SQLException
      Import data from a given file into the specified table columns from the specified columns in the file. Data for large object columns is in an external file, the reference to it is in the main input file. Read the lob data from the external file using the lob location info in the main import file.

      Will be called as SYSCS_IMPORT_DATA_LOBS_FROM_EXTFILE(IN SCHEMANAME VARCHAR(128), IN TABLENAME VARCHAR(128), IN INSERTCOLUMNLIST VARCHAR(32672), IN COLUMNINDEXES VARCHAR(32672), IN FILENAME VARCHAR(32672), IN COLUMNDELIMITER CHAR(1), IN CHARACTERDELIMITER CHAR(1), IN CODESET VARCHAR(128), IN REPLACE SMALLINT)

      Throws:
      SQLException - if a database error occurs
    • SYSCS_BULK_INSERT

      public static void SYSCS_BULK_INSERT(String schemaName, String tableName, String vtiName, String vtiArg) throws SQLException
      Perform bulk insert using the specificed vti .

      Will be called as SYSCS_BULK_INSERT (IN SCHEMANAME VARCHAR(128), IN TABLENAME VARCHAR(128), IN VTINAME VARCHAR(32672), IN VTIARG VARCHAR(32672))

      Throws:
      SQLException - if a database error occurs
    • SYSCS_RELOAD_SECURITY_POLICY

      public static void SYSCS_RELOAD_SECURITY_POLICY() throws SQLException
      Reload the policy file.

      System procedure called thusly: SYSCS_UTIL.SYSCS_RELOAD_SECURITY_POLICY()

      Throws:
      SQLException
    • PI

      public static double PI()
      Method to return the constant PI. SYSFUN.PI().
      Returns:
      PI
    • COT

      public static double COT(double value)
      Cotangent function. SYSFUN.COT
      Returns:
      1 / tan(x)
      See Also:
    • SIGN

      public static int SIGN(double value)
      Method to return the sign of the given value. SYSFUN.SIGN().
      Returns:
      0, 1 or -1
    • RAND

      public static double RAND(int seed)
      Pseudo-random number function.
      Returns:
      a random number
    • SYSCS_SET_USER_ACCESS

      public static void SYSCS_SET_USER_ACCESS(String userName, String connectionPermission) throws SQLException
      Set the connection level authorization for a specific user - SYSCS_UTIL.SYSCS_SET_USER_ACCESS.
      Parameters:
      userName - name of the user in its normal form (not a SQL identifier).
      connectionPermission -
      Throws:
      SQLException - Error setting the permission
    • removeFromAccessList

      private static void removeFromAccessList(String listProperty, String userName) throws SQLException, StandardException
      Utility method for SYSCS_SET_USER_ACCESS removes a user from one of the access lists, driven by the property name.
      Throws:
      SQLException
      StandardException
    • SYSCS_GET_USER_ACCESS

      public static String SYSCS_GET_USER_ACCESS(String userName) throws SQLException
      Get the connection level authorization for a specific user - SYSCS_UTIL.SYSCS_GET_USER_ACCESS.
      Parameters:
      userName - name of the user in its normal form (not a SQL identifier).
      Throws:
      SQLException
    • SYSCS_INVALIDATE_STORED_STATEMENTS

      public static void SYSCS_INVALIDATE_STORED_STATEMENTS() throws SQLException
      Invalidate all the stored statements so they will get recompiled when executed next time around.
      Throws:
      SQLException
    • SYSCS_EMPTY_STATEMENT_CACHE

      public static void SYSCS_EMPTY_STATEMENT_CACHE() throws SQLException
      Empty as much of the cache as possible. It is not guaranteed that the cache is empty after this call, as statements may be kept by currently executing queries, activations that are about to be garbage collected.
      Throws:
      SQLException
    • SYSCS_SET_XPLAIN_MODE

      public static void SYSCS_SET_XPLAIN_MODE(int mode) throws SQLException, StandardException
      this procedure switches between the different xplain modes
      Parameters:
      mode - either 0 for explain only, or 1 for explain and execute (default)
      Throws:
      SQLException
      StandardException
    • SYSCS_GET_XPLAIN_MODE

      public static int SYSCS_GET_XPLAIN_MODE() throws SQLException, StandardException
      This procedure returns the current status of the xplain mode. If the XPLAIN mode is non-zero, meaning that it is ON, then statements are being XPLAIN'd only, not executed.
      Returns:
      0 if XPLAIN mode is off, non-zero if on.
      Throws:
      SQLException
      StandardException
    • SYSCS_SET_XPLAIN_SCHEMA

      public static void SYSCS_SET_XPLAIN_SCHEMA(String schemaName) throws SQLException, StandardException
      This procedure sets the current xplain schema. If the schema is not set, runtime statistics are captured as a textual stream printout. If it is set, statisitcs information is stored in that schema in user tables.
      Parameters:
      schemaName - May be an empty string.
      Throws:
      SQLException
      StandardException
    • hasSchema

      private static boolean hasSchema(Connection conn, String schemaName) throws SQLException
      Throws:
      SQLException
    • hasTable

      private static boolean hasTable(Connection conn, String schemaName, String tableName) throws SQLException
      Throws:
      SQLException
    • createXplainSchema

      private static void createXplainSchema(String schemaName) throws SQLException
      Throws:
      SQLException
    • createXplainTable

      private static void createXplainTable(LanguageConnectionContext lcc, String schemaName, XPLAINTableDescriptor t) throws SQLException
      Throws:
      SQLException
    • SYSCS_GET_XPLAIN_SCHEMA

      public static String SYSCS_GET_XPLAIN_SCHEMA() throws SQLException, StandardException
      This procedure returns the current set XPLAIN_SCHEMA
      Returns:
      schema name, may be blank if no schema currently set.
      Throws:
      SQLException
      StandardException
    • SYSCS_CREATE_USER

      public static void SYSCS_CREATE_USER(String userName, String password) throws SQLException
      Create a new user.
      Throws:
      SQLException
    • addUser

      public static void addUser(String userName, String password, TransactionController tc) throws SQLException
      Create a new user (this entry is called when bootstrapping the credentials of the DBO at database creation time.
      Throws:
      SQLException
    • makeUserDescriptor

      private static UserDescriptor makeUserDescriptor(DataDictionary dd, TransactionController tc, String userName, String password) throws StandardException
      Throws:
      StandardException
    • SYSCS_RESET_PASSWORD

      public static void SYSCS_RESET_PASSWORD(String userName, String password) throws SQLException
      Reset a user's password.
      Throws:
      SQLException
    • resetAuthorizationIDPassword

      private static void resetAuthorizationIDPassword(String userName, String password) throws SQLException
      Reset the password for an already normalized authorization id.
      Throws:
      SQLException
    • SYSCS_MODIFY_PASSWORD

      public static void SYSCS_MODIFY_PASSWORD(String password) throws SQLException
      Change a user's password.
      Throws:
      SQLException
    • SYSCS_DROP_USER

      public static void SYSCS_DROP_USER(String userName) throws SQLException
      Drop a user.
      Throws:
      SQLException
    • checkLegalUser

      private static void checkLegalUser(DataDictionary dd, String userName) throws StandardException
      Raise an exception if the user doesn't exist. See commentary on DERBY-5648.
      Throws:
      StandardException
    • normalizeUserName

      private static String normalizeUserName(String userName) throws SQLException
      Normalize the user name so that there is only one set of credentials for a given authorization id.
      Throws:
      SQLException
    • SYSCS_GET_DATABASE_NAME

      public static String SYSCS_GET_DATABASE_NAME() throws SQLException
      Return the database name
      Returns:
      database name
      Throws:
      SQLException
    • SYSCS_PEEK_AT_SEQUENCE

      public static Long SYSCS_PEEK_AT_SEQUENCE(String schemaName, String sequenceName) throws SQLException
      Peek at the current value of a sequence generator without advancing it.
      Parameters:
      schemaName - The name of the schema holding the sequence.
      sequenceName - The name of the sequence in that schema.
      Throws:
      SQLException - if a database error occurs
    • SYSCS_PEEK_AT_IDENTITY

      public static Long SYSCS_PEEK_AT_IDENTITY(String schemaName, String tableName) throws SQLException
      Peek at the current value of an identity generator without advancing it.
      Parameters:
      schemaName - The name of the schema holding the table.
      tableName - The name of the table in that schema.
      Throws:
      SQLException - if a database error occurs
    • getMonitor

      private static ModuleFactory getMonitor()
      Helper routine which looks up the monitor.