1- Drop the "aus_setup_table" procedure in the sysadmin
database. ( > drop procedure aus_setup_table;)
2- recreate the procedure running the followin :
CREATE FUNCTION "informix".aus_setup_table(inHDRmode INTEGER)
RETURNING integer
DEFINE errnum INTEGER;
--TRACE "FUNCTION aus_setup_table()" ;
/* Cleanup all the work tables */
BEGIN
ON EXCEPTION IN ( -310 ) SET errnum -- table already
exists
END EXCEPTION WITH RESUME
CREATE TABLE informix.aus_cmd_info
(
aus_ci_dbs_partnum INTEGER,
aus_ci_stime DATETIME YEAR TO SECOND
DEFAULT CURRENT YEAR TO
SECOND,
aus_ci_etime DATETIME YEAR TO SECOND
DEFAULT NULL,
aus_ci_database VARCHAR(255) DEFAULT NULL,
aus_ci_locale VARCHAR(36) DEFAULT NULL,
aus_ci_logmode CHAR(1) DEFAULT NULL,
aus_ci_missed_tables INTEGER DEFAULT 0,
aus_ci_need_tables INTEGER DEFAULT 0,
aus_ci_done_tables INTEGER DEFAULT 0
);
INSERT INTO
aus_cmd_info(aus_ci_dbs_partnum,aus_ci_stime)
VALUES (0,aus_get_realtime());
IF inHDRmode = 1 THEN
CREATE UNIQUE INDEX informix.aus_cmd_info_index1
ON aus_cmd_info(aus_ci_dbs_partnum) ONLINE;
CREATE UNIQUE INDEX informix.aus_cmd_info_index2
ON aus_cmd_info(aus_ci_database) ONLINE;
ELSE
CREATE UNIQUE INDEX informix.aus_cmd_info_index1
ON aus_cmd_info(aus_ci_dbs_partnum);
CREATE UNIQUE INDEX informix.aus_cmd_info_index2
ON aus_cmd_info(aus_ci_database);
END IF;
CREATE TABLE informix.aus_command
(
aus_cmd_id SERIAL,
aus_cmd_state CHAR(1) DEFAULT 'P'
CHECK (aus_cmd_state IN
("P","I","E","C")),
-- P => Command is pending
-- I => Command is inprogress
-- E => Command had an Error
-- C => Command is complete
w/o Errors
aus_cmd_type CHAR(1),
aus_cmd_dbs_priority SMALLINT DEFAULT 2,
aus_cmd_priority BIGINT,
aus_cmd_dbs_partnum INTEGER,
aus_cmd_partnum INTEGER,
aus_cmd_err_sql INTEGER,
aus_cmd_err_isam INTEGER,
aus_cmd_time DATETIME YEAR TO SECOND
DEFAULT CURRENT YEAR TO SECOND,
aus_cmd_runtime INTERVAL HOUR TO SECOND
DEFAULT NULL,
aus_cmd_exe LVARCHAR(8192)
) LOCK MODE ROW;
CREATE VIEW informix.aus_cmd_list AS SELECT
aus_cmd_id, aus_cmd_type,
aus_cmd_priority, aus_cmd_dbs_partnum,
aus_cmd_partnum, aus_cmd_exe
FROM aus_command
WHERE aus_cmd_state = 'P';
CREATE VIEW informix.aus_cmd_comp AS SELECT
aus_cmd_id, aus_cmd_type,
aus_cmd_priority, aus_cmd_dbs_partnum,
aus_cmd_partnum, aus_cmd_exe,
aus_cmd_time
FROM aus_command
WHERE aus_cmd_state = 'C';
END
BEGIN
ON EXCEPTION IN ( -310 ) SET errnum -- table already
exists
END EXCEPTION WITH RESUME
/*
* This table contains both
* index columns as wells as
* user defined distribution columns
*/
CREATE TABLE informix.aus_work_icols
(
aus_icols_tabid integer,
aus_icols_colno integer,
aus_icols_lkey char(1) CHECK( aus_icols_lkey IN
('Y','N')),
aus_icols_mode char(1) DEFAULT NULL,
aus_icols_colname varchar(128)
) LOCK MODE ROW;
CREATE TABLE informix.aus_work_info
(
aus_info_id SERIAL,
aus_info_db_partnum INTEGER,
aus_info_tabname VARCHAR(128),
aus_info_tabid INTEGER,
aus_info_partnum INTEGER,
aus_info_ustlowts DATETIME YEAR TO SECOND,
aus_info_npused BIGINT,
aus_info_nrows BIGINT,
aus_info_nindexes smallint
) LOCK MODE ROW;
CREATE TABLE informix.aus_work_dist
(
aus_dist_id serial,
aus_dist_tabid INTEGER, -- this is the
tabid
aus_dist_colno INTEGER,
aus_dist_mode CHAR(1),
aus_dist_resolution FLOAT,
aus_dist_confidence FLOAT,
aus_dist_smplsize float,
aus_dist_rowssmplde BIGINT,
aus_dist_constr_time DATETIME YEAR TO SECOND,
aus_dist_ustnrows BIGINT
) LOCK MODE ROW;
END
RETURN 1;
END FUNCTION;
3- Test your reproduction |
