newbie with problems restoring db
Daniel
I have one machine running IDS 9.21 (?) on Solaris (7?). My objective was to
do take one of the databases and all its data and put it on another machine
running IDS 9.4 on Windows XP.
I was seemingly able to grab the data by using dbexport. I got a directory
with a *.sql file and a whole bunch of *.unl files. I then moved this to my
Windows machine and tried to use dbimport to recreate the db and its
information. The process runs for a while, and then stops with an error:
"202 - An illegal character has been found in the statement."
This is the command I ran in Windows:
C:\Database\informix_backup>\database\informix\bin\dbimport jfacts -c -i
c:\database\informix_backup
What can I do to fix this?
The output of the dbimport is as follows, from dbimport.out :
Thanks!
{ DATABASE jfacts delimiter | }
grant dba to "informix";
grant connect to "jfactusr";
CREATE PROCEDURE "informix".sp_modify_user_id_data_type()
RETURNING VARCHAR(50);
{ NAME: Chi Dinh
DATE: June 25, 2002
DESCRIPTION: change create_user_id and last_update_user_id
from char(10) to varchar(10) for every table in jfacts.
}
DEFINE p_tabname VARCHAR(50);
DEFINE p_max_length INT;
DEFINE p_begin_len INT;
--DROP TABLE TEMP1;
-- select * from temp1;
FOREACH
SELECT 'tabname_code'
INTO p_tabname
FROM systables
WHERE UPPER(locklevel) = 'P'
AND UPPER(tabname) NOT LIKE ('Z%')
LET p_max_length = LENGTH(p_tabname);
LET p_begin_len = p_max_length - 3;
IF p_tabname[8, 11] = 'code' THEN
ALTER TABLE p_tabname
MODIFY last_update_user_id VARCHAR(10) NOT NULL;
ELSE
ALTER TABLE p_tabname
MODIFY last_update_user_id VARCHAR(10) NOT NULL;
ALTER TABLE p_tabname
MODIFY create_user_id VARCHAR(10) NOT NULL;
END IF;
RETURN p_tabname WITH RESUME;
END FOREACH;
END PROCEDURE;
CREATE PROCEDURE "informix".sp_rent_summary_charge(i_Month INTEGER,i_Year
INTEGER)
RETURNING INTEGER, INTEGER, FLOAT, INTEGER, FLOAT;
DEFINE o_rentBillID INT;
DEFINE o_sqftChargeBasis INT;
DEFINE o_sqftChargeAmt FLOAT;
DEFINE o_parkChargeBasis INT;
DEFINE o_parkChargeAmt FLOAT;
FOREACH
SELECT r.rent_bill_id ,
sum(CASE
WHEN (c.charge_type_code = 10 ) THEN c.charge_basis
WHEN (c.charge_type_code = 11 ) THEN c.charge_basis
WHEN (c.charge_type_code = 12 ) THEN c.charge_basis
ELSE 0
END ) as sqft_charge_basis,
sum(CASE
WHEN (c.charge_type_code = 120 ) THEN 0
WHEN (c.charge_type_code = 130 ) THEN 0
ELSE c.charge_amount
END ) as sqft_charge_amount,
sum(CASE
WHEN (c.charge_type_code = 120 ) THEN c.charge_basis
WHEN (c.charge_type_code = 130 ) THEN c.charge_basis
ELSE 0
END ) as parking_charge_basis,
sum(CASE
WHEN (c.charge_type_code = 120 ) THEN c.charge_amount
WHEN (c.charge_type_code = 130 ) THEN c.charge_amount
ELSE 0
END ) as parking_charge_amount
INTO o_rentBillID, o_sqftChargeBasis,
o_sqftChargeAmt, o_parkChargeBasis,
o_parkChargeAmt
FROM rent_bill_charge c,
rent_bill r
WHERE month(r.rent_bill_date) = i_Month
AND year(r.rent_bill_date) = i_Year
AND r.rent_bill_id = c.rent_bill_id
GROUP BY r.rent_bill_id
RETURN o_rentBillID,
o_sqftChargeBasis,
o_sqftChargeAmt,
o_parkChargeBasis,
o_parkChargeAmt
WITH RESUME;
END FOREACH;
END PROCEDURE;
CREATE PROCEDURE "informix".sp_rent_summary_charge2(i_Month INTEGER,i_Year
INTEGER)
RETURNING INTEGER;
DEFINE o_rentBillID INTEGER;
FOREACH
SELECT rent_bill_id
INTO o_rentBillID
FROM rent_bill
WHERE month(rent_bill_date) = i_Month
AND year(rent_bill_date) = i_Year
RETURN o_rentBillID
WITH RESUME;
END FOREACH;
END PROCEDURE;
CREATE PROCEDURE "informix".sp_space_usage_summary(i_Month CHAR(2),i_Year
CHAR(4),i_Circuit VARCHAR(2))
RETURNING CHAR(7),VARCHAR(50), INT, FLOAT,
FLOAT, FLOAT, FLOAT, FLOAT, FLOAT, FLOAT;
DEFINE o_org_code CHAR(7);
DEFINE o_org_long_name VARCHAR(50);
DEFINE o_num_personnel INT;
DEFINE o_num_allocated_staff FLOAT;
DEFINE o_sqft_rentable FLOAT;
DEFINE o_sqft_usable FLOAT;
DEFINE o_space_per_person FLOAT;
DEFINE o_space_per_work_unit FLOAT;
DEFINE o_usable_space_per_person FLOAT;
DEFINE o_usable_space_per_work_unit FLOAT;
CREATE TEMP TABLE zREPORT_RENT_BILL_SUM (
org_code CHAR(7) ,
org_long_name VARCHAR(50) default 'Missing Org Name',
sqft_rentable FLOAT default 0.0,
sqft_usable FLOAT default 0.0
) WITH NO LOG;
CREATE TEMP TABLE zREPORT_PERSONNEL_SUM (
org_code CHAR(7) ,
num_personnel INT default 0
) WITH NO LOG;
CREATE TEMP TABLE zREPORT_STAFF_ALLOT_SUM (
org_code CHAR(7) ,
num_allocated_staff FLOAT default 0.0
) WITH NO LOG;
-- insert Rent Bill and CBR data
-- for each organization
INSERT INTO zREPORT_RENT_BILL_SUM(org_code,org_long_name, sqft_rentable,
sqft_usable)
SELECT o.org_code,
o.org_long_name,
sum(nvl(c.sqft_rentable,0)),
--sum(nvl(r.cbr_usable,0)) as sqft_usable
sum(nvl(c.sqft_usable,0))
FROM RENT_BILL r,
ORGANIZATION o,
CBR c
WHERE year(r.rent_bill_date) = i_Year
AND month(r.rent_bill_date) = i_Month
AND nvl(r.circuit,'XX') like i_Circuit
AND o.is_court_unit_tracked = 1
AND r.ab_code = o.ab_code
AND nvl(r.circuit,'XX') = o.org_code[2,3]
AND nvl(r.district,'XXX') = o.org_code[4,6]
AND r.cbr_number = c.cbr_number
GROUP BY 1,2;
-- insert personnel summary records;
-- org codes with unit types 'F' (federal defenders)
-- and 'V' (voice) are group seperately;
-- all other org codes are grouped
-- under unit type 'X' (summary)
INSERT INTO zREPORT_PERSONNEL_SUM(org_code,num_personnel)
SELECT CASE WHEN org_code[7,7] = 'F' OR org_code[7,7] = 'V' THEN org_code
ELSE org_code[1,6] || 'X'
END,
sum(nvl(num_personnel,0))
FROM PERSONNEL_SUMMARY
WHERE pay_month_year[1,4] = i_Year
AND pay_month_year[5,6] = i_Month
AND org_code[2,3] like i_Circuit
GROUP BY 1;
-- insert staff allotment records;
-- org codes with unit type 'F' (federal defenders)
-- are grouped seperately; all other org codes
-- are grouped under unit type 'X' (summary)
INSERT INTO zREPORT_STAFF_ALLOT_SUM(org_code,num_allocated_staff)
SELECT CASE WHEN staff_type_code[1,1] = 'F' THEN prefix_org_code ||
staff_type_code[1,1]
ELSE prefix_org_code ||'X'
END,
sum(nvl(num_allocated_staff,0))
FROM STAFF_ALLOT_SUMMARY
WHERE allotment_date[1,4] = i_Year
AND allotment_date[5,6] = i_Month
AND prefix_org_code[2,3] like i_Circuit
GROUP BY 1;
FOREACH
SELECT r.org_code,
r.org_long_name,
nvl(p.num_personnel,0),
nvl(s.num_allocated_staff,0) ,
nvl(r.sqft_rentable,0) ,
nvl(r.sqft_usable,0),
CASE WHEN nvl(p.num_personnel,0) = 0 THEN 0
ELSE nvl(r.sqft_rentable,0) / p.num_personnel
END as space_per_person,
CASE WHEN nvl(s.num_allocated_staff,0) = 0 THEN 0
ELSE nvl(r.sqft_rentable,0) / s.num_allocated_staff
END as space_per_work_unit,
CASE WHEN nvl(p.num_personnel,0) = 0 THEN 0
ELSE nvl(r.sqft_usable,0) / p.num_personnel
END as usable_space_per_person,
CASE WHEN nvl(s.num_allocated_staff,0) = 0 THEN 0
ELSE nvl(r.sqft_usable,0) / s.num_allocated_staff
END as usable_space_per_work_unit
INTO o_org_code,
o_org_long_name,
o_num_personnel,
o_num_allocated_staff,
o_sqft_rentable,
o_sqft_usable,
o_space_per_person,
o_space_per_work_unit,
o_usable_space_per_person,
o_usable_space_per_work_unit
FROM zREPORT_RENT_BILL_SUM r,
OUTER zREPORT_PERSONNEL_SUM p,
OUTER zREPORT_STAFF_ALLOT_SUM s
WHERE r.org_code = p.org_code
AND r.org_code = s.org_code
ORDER BY 1,2
RETURN o_org_code,
o_org_long_name,
o_num_personnel,
round(o_num_allocated_staff,1),
round(o_sqft_rentable),
round(o_sqft_usable),
round(o_space_per_person,3),
round(o_space_per_work_unit,3),
round(o_usable_space_per_person,3),
round(o_usable_space_per_work_unit,3)
WITH RESUME;
END FOREACH;
DROP TABLE zREPORT_RENT_BILL_SUM;
DROP TABLE zREPORT_STAFF_ALLOT_SUM;
DROP TABLE zREPORT_PERSONNEL_SUM;
END PROCEDURE;
CREATE PROCEDURE "informix".sp_rent_summary_budget_cost(i_Year CHAR(4))
RETURNING CHAR(7),CHAR(4), CHAR(4), INT, CHAR(4),
FLOAT, FLOAT, FLOAT, FLOAT, FLOAT, FLOAT,
CHAR(6);
DEFINE o_cost_class CHAR(7);
DEFINE o_calendar_year CHAR(4);
DEFINE o_fiscal_year CHAR(4);
DEFINE o_rent_month INT;
DEFINE o_ab_code CHAR(4);
DEFINE o_sqft_space FLOAT;
DEFINE o_sqft_cost FLOAT;
DEFINE o_parking_space FLOAT;
DEFINE o_parking_cost FLOAT;
DEFINE o_current_adjustment FLOAT;
DEFINE o_prior_adjustment FLOAT;
DEFINE o_fund_code CHAR(6);
CREATE TEMP TABLE zREPORT_RENT_SUM_BUDGET_COST (
cost_class CHAR(7) ,
calendar_year CHAR(4),
fiscal_year CHAR(4),
rent_month INT,
ab_code CHAR(4),
sqft_space FLOAT default 0.0,
sqft_cost FLOAT default 0.0,
parking_space FLOAT default 0.0,
parking_cost FLOAT default 0.0,
current_adjustment FLOAT default 0.0,
prior_adjustment FLOAT default 0.0
,
fund_code CHAR(6)
) WITH NO LOG;
INSERT INTO zREPORT_RENT_SUM_BUDGET_COST (cost_class,
calendar_year,fiscal_year,rent_month,ab_code,
sqft_space,sqft_cost,parking_space,parking_cost,fund_code )
SELECT bcc.cost_class,
year(rb.rent_bill_date) calendar_year,
CASE WHEN month(rb.rent_bill_date) > 9 THEN
year(rb.rent_bill_date)+1
ELSE year(rb.rent_bill_date)
END fiscal_year,
month(rb.rent_bill_date) rent_month,
rb.ab_code,
sum(CASE WHEN (rbc.charge_type_code = 10 ) THEN rbc.charge_basis
WHEN (rbc.charge_type_code = 11 ) THEN rbc.charge_basis
WHEN (rbc.charge_type_code = 12 ) THEN rbc.charge_basis
ELSE 0
END ) sqft_space,
sum(CASE WHEN (rbc.charge_type_code = 120 ) THEN 0
WHEN (rbc.charge_type_code = 130 ) THEN 0
ELSE rbc.charge_amount
END ) sqft_cost,
sum(CASE WHEN (rbc.charge_type_code = 120 ) THEN rbc.charge_basis
WHEN (rbc.charge_type_code = 130 ) THEN rbc.charge_basis
ELSE 0
END ) parking_space,
sum(CASE WHEN (rbc.charge_type_code = 120 ) THEN rbc.charge_amount
WHEN (rbc.charge_type_code = 130 ) THEN rbc.charge_amount
ELSE 0
END ) parking_cost,
bcc.fund_code
FROM RENT_BILL rb,
BUDGET_COST_CENTER bcc,
BUILDING bg,
RENT_BILL_CHARGE rbc
WHERE ( (month(rb.rent_bill_date) > 9 AND year(rb.rent_bill_date)+1 =
i_Year) OR
(month(rb.rent_bill_date) < 10 AND year(rb.rent_bill_date) =
i_Year) )
AND rb.ab_code = bcc.ab_code
AND rb.building_id = bg.building_id
AND bg.building_number = bcc.state_code || bcc.building_location
AND rb.rent_bill_id = rbc.rent_bill_id
GROUP BY rb.rent_bill_date,
bcc.cost_class,
rb.ab_code,
bcc.fund_code;
INSERT INTO zREPORT_RENT_SUM_BUDGET_COST (cost_class,
calendar_year,fiscal_year,rent_month,ab_code,
current_adjustment,prior_adjustment,fund_code )
SELECT bcc.cost_class,
year(ra.rent_bill_date) calendar_year,
CASE WHEN month(ra.rent_bill_date) > 9 THEN year(ra.rent_bill_date)+1
ELSE year(ra.rent_bill_date)
END fiscal_year,
month(ra.rent_bill_date),
ra.ab_code,
sum(CASE WHEN (ra.charge_type_code = 190 ) THEN ra.charge_amount
ELSE 0
END ) current_adjustment,
sum(CASE WHEN (ra.charge_type_code = 200 ) THEN ra.charge_amount
ELSE 0
END ) prior_adjustment,
bcc.fund_code
FROM BUDGET_COST_CENTER bcc,
BUILDING bg,
RENT_ADJUSTMENT ra
WHERE ( (month(ra.rent_bill_date) > 9 AND year(ra.rent_bill_date)+1 =
i_Year) OR
(month(ra.rent_bill_date) < 10 AND year(ra.rent_bill_date) =
i_Year) )
AND ra.charge_type_code in (190,200)
AND bg.building_number = bcc.state_code || building_location
AND ra.ab_code = bcc.ab_code
AND ra.building_id = bg.building_id
GROUP BY ra.rent_bill_date,
cost_class,
ra.ab_code,
bcc.fund_code;
FOREACH
SELECT cost_class,
calendar_year,
fiscal_year,
rent_month,
ab_code,
sum(sqft_space),
sum(sqft_cost),
sum(parking_space),
sum(parking_cost),
sum(current_adjustment),
sum(prior_adjustment)
,
fund_code
INTO o_cost_class,
o_calendar_year,
o_fiscal_year,
o_rent_month,
o_ab_code,
o_sqft_space,
o_sqft_cost,
o_parking_space,
o_parking_cost,
o_current_adjustment,
o_prior_adjustment
,
o_fund_code
FROM zREPORT_RENT_SUM_BUDGET_COST
GROUP BY 1,2,3,4,5
,12
ORDER BY 2,4,12,1,5
RETURN o_cost_class,
o_calendar_year,
o_fiscal_year,
o_rent_month,
o_ab_code,
round(o_sqft_space,2),
round(o_sqft_cost,2),
round(o_parking_space,2),
round(o_parking_cost,2),
round(o_current_adjustment,2),
round(o_prior_adjustment,2)
,
o_fund_code
WITH RESUME;
END FOREACH;
DROP TABLE zREPORT_RENT_SUM_BUDGET_COST;
END PROCEDURE;
grant execute on function "informix".sp_modify_user_id_data_type () to
"public" as "informix";
grant execute on function "informix".sp_rent_summary_charge
(integer,integer) to "public" as "informix";
grant execute on function "informix".sp_rent_summary_charge2
(integer,integer) to "public" as "informix";
grant execute on function "informix".sp_space_usage_summary
(char,char,varchar) to "public" as "informix";
grant execute on function "informix".sp_rent_summary_budget_cost (char) to
"public" as "informix";
{ TABLE "informix".zagency_bureau_code row size = 227 number of columns = 8
index
size = 0 }
{ unload file name = zagen00168.unl number of rows = 42 }
create table "informix".zagency_bureau_code
(
abcode char(4),
bureau_name varchar(80),
court_name varchar(30),
court_type char(1),
unit_type char(1),
lease_term integer,
space_desc varchar(100),
ab_rollup char(4)
);
revoke all on "informix".zagency_bureau_code from "public";
{ TABLE "informix".zcircuit row size = 509 number of columns = 10 index size
= 0
}
{ unload file name = zcirc00172.unl number of rows = 16 }
create table "informix".zcircuit
(
code char(2),
id integer,
longname varchar(100),
shortname varchar(100),
addr1 varchar(100),
addr2 varchar(100),
city varchar(35),
state char(2),
zip varchar(9),
name varchar(50)
);
revoke all on "informix".zcircuit from "public";
{ TABLE "informix".zgsa_region_code row size = 156 number of columns = 7
index size
= 0 }
{ unload file name = zgsa_00174.unl number of rows = 12 }
create table "informix".zgsa_region_code
(
code integer,
state_code char(2),
gsa_desc varchar(35),
name char(2),
street varchar(50),
city varchar(50),
zip varchar(9)
);
revoke all on "informix".zgsa_region_code from "public";
{ TABLE "informix".zcharge_type_code row size = 54 number of columns = 2
index size
= 0 }
{ unload file name = zchar00175.unl number of rows = 0 }
create table "informix".zcharge_type_code
(
charge_type char(3),
charge_desc varchar(50)
);
revoke all on "informix".zcharge_type_code from "public";
{ TABLE "informix".zdistrict row size = 134 number of columns = 9 index size
= 0
}
{ unload file name = zdist00177.unl number of rows = 94 }
create table "informix".zdistrict
(
district char(3),
circuit char(2),
acr varchar(5),
long varchar(50),
short varchar(25),
size integer,
hq varchar(35),
sort integer,
state_code char(2)
);
revoke all on "informix".zdistrict from "public";
{ TABLE "informix".zcontractor_code row size = 330 number of columns = 14
index size
= 0 }
{ unload file name = zcont00180.unl number of rows = 43 }
create table "informix".zcontractor_code
(
code char(3),
contract_number varchar(15),
co_name varchar(25),
street varchar(50),
city varchar(35),
zip varchar(9),
poc_name varchar(50),
alt_name varchar(50),
phone char(13),
email_address varchar(50),
fax varchar(13),
contractor_type char(2),
state_code char(2),
is_active integer
);
revoke all on "informix".zcontractor_code from "public";
{ TABLE "informix".zorganization row size = 161 number of columns = 11 index
size
= 0 }
{ unload file name = zorga00195.unl number of rows = 1282 }
create table "informix".zorganization
(
org_code char(7),
dvsn_class_code varchar(35),
org_long_name varchar(50),
org_short_name varchar(50),
circuit char(2),
district char(3),
court_type_code char(1),
unit_type_code char(1),
ab_code char(4),
is_dup integer,
extra_char char(1)
);
revoke all on "informix".zorganization from "public";
{ TABLE "informix".zerr_boc_code row size = 360 number of columns = 6 index
size
= 0 }
{ unload file name = zerr_00202.unl number of rows = 29 }
create table "informix".zerr_boc_code
(
code char(4),
boc_desc varchar(255),
name varchar(35),
budget_org char(7),
fund_code char(6),
comments varchar(50)
);
revoke all on "informix".zerr_boc_code from "public";
{ TABLE "informix".zboc_code row size = 309 number of columns = 5 index size
= 0
}
{ unload file name = zboc_00203.unl number of rows = 42 }
create table "informix".zboc_code
(
code char(4),
boc_desc varchar(255),
name varchar(35),
budget_org char(7),
fund_code char(6)
);
revoke all on "informix".zboc_code from "public";
{ TABLE "informix".zbuilding row size = 368 number of columns = 30 index
size = 0
}
{ unload file name = zbuil00206.unl number of rows = 1559 }
create table "informix".zbuilding
(
num_phone integer,
sat_install_dt date,
court_phone_install_dt date,
loc_building_no char(6),
district char(3),
division varchar(35),
circuit char(2),
building_name varchar(50)
default 'UNKNOWN',
address_gsa varchar(50)
default 'UNKNOWN',
gsa_city varchar(35),
state_code char(2),
zip varchar(9),
metro_stat_area_code char(4),
building_owner_code char(1),
sqft_usable float,
office_space_capacity float,
switch_type char(10),
phone_switch_manu_code char(1),
court_phone_install_date date,
num_security_posts integer,
usmarshal_bldg_num char(8),
num_outside_parking integer,
ru_factor float,
county varchar(30),
gsa_region_code integer,
built_date date,
is_active char(1),
num_floors integer,
is_level_4_security_rating integer,
address_postal varchar(50)
default 'UNKNOWN'
);
revoke all on "informix".zbuilding from "public";
{ TABLE "informix".zerr_building row size = 624 number of columns = 31 index
size
= 0 }
{ unload file name = zerr_00207.unl number of rows = 21 }
create table "informix".zerr_building
(
num_phone integer,
sat_install_dt date,
court_phone_install_dt date,
loc_building_no char(6),
district char(3),
division varchar(35),
circuit char(2),
building_name varchar(50),
address_gsa varchar(50),
gsa_city varchar(35),
state_code char(2),
zip varchar(9),
metro_stat_area_code char(4),
building_owner_code char(1),
sqft_usable float,
office_space_capacity float,
switch_type char(10),
phone_switch_manu_code char(1),
court_phone_install_date date,
num_security_posts integer,
usmarshal_bldg_num char(8),
num_outside_parking integer,
ru_factor float,
county varchar(30),
gsa_region_code integer,
built_date date,
is_active char(1),
num_floors integer,
is_level_4_security_rating integer,
address_postal varchar(50),
comments varchar(255)
);
revoke all on "informix".zerr_building from "public";
{ TABLE "informix".zcourtroom row size = 92 number of columns = 17 index
size = 0
}
{ unload file name = zcour00209.unl number of rows = 0 }
create table "informix".zcourtroom
(
audio_install_date date,
building_no char(6),
condition_code char(1),
contractor_code char(3),
is_occupied integer,
courtroom_type_code char(7),
eps_config_code char(1),
eps_install_date date,
primary_use_code char(1),
num_jury_box_capacity integer,
org_code char(7),
update_date datetime year to second,
update_user_id char(10),
release_date date,
room_number varchar(15),
size_sqft float,
video_install_date date
);
revoke all on "informix".zcourtroom from "public";
{ TABLE "informix".zerr_courtroom row size = 348 number of columns = 18
index size
= 0 }
{ unload file name = zerr_00210.unl number of rows = 12 }
create table "informix".zerr_courtroom
(
audio_install_date date,
building_no char(6),
condition_code char(1),
contractor_code char(3),
is_occupied integer,
courtroom_type_code char(7),
eps_config_code char(1),
eps_install_date date,
primary_use_code char(1),
num_jury_box_capacity integer,
org_code char(7),
update_date datetime year to second,
update_user_id char(10),
release_date date,
room_number varchar(15),
size_sqft float,
video_install_date date,
comments varchar(255)
);
revoke all on "informix".zerr_courtroom from "public";
{ TABLE "informix".zjudge_type_code row size = 84 number of columns = 4
index size
= 0 }
{ unload file name = zjudg00211.unl number of rows = 36 }
create table "informix".zjudge_type_code
(
judge_type_code char(6),
judge_type_desc varchar(50),
court_name varchar(25),
primary_use_code char(1)
);
revoke all on "informix".zjudge_type_code from "public";
{ TABLE "informix".zjudge row size = 82 number of columns = 10 index size =
0 }
{ unload file name = zjudg00212.unl number of rows = 2077 }
create table "informix".zjudge
(
budget_cat_code char(4),
building_number char(6),
circuit char(2),
district char(3),
judge_id_number integer,
judge_type_code char(6),
first_name varchar(25),
last_name varchar(25),
mid_initial char(1),
name_suffix varchar(3)
);
revoke all on "informix".zjudge from "public";
{ TABLE "informix".zerr_judge row size = 183 number of columns = 11 index
size =
0 }
{ unload file name = zerr_00216.unl number of rows = 36 }
create table "informix".zerr_judge
(
budget_cat_code char(4),
building_number char(6),
circuit char(2),
district char(3),
judge_id_number integer,
judge_type_code char(6),
first_name varchar(25),
last_name varchar(25),
mid_initial char(1),
name_suffix varchar(3),
comments varchar(100)
);
revoke all on "informix".zerr_judge from "public";
{ TABLE "informix".zchambers row size = 43 number of columns = 7 index size
= 0 }
{ unload file name = zcham00219.unl number of rows = 2832 }
create table "informix".zchambers
(
building_number char(6),
is_active integer,
judge_legacy_id integer,
release_date date,
room_number varchar(15),
sqft_chambers float,
primary_use_code char(1)
);
revoke all on "informix".zchambers from "public";
{ TABLE "informix".zerr_chambers row size = 144 number of columns = 8 index
size
= 0 }
{ unload file name = zerr_00220.unl number of rows = 58 }
create table "informix".zerr_chambers
(
building_number char(6),
is_active integer,
judge_legacy_id integer,
release_date date,
room_number varchar(15),
sqft_chambers float,
primary_use_code char(1),
comments varchar(100)
);
revoke all on "informix".zerr_chambers from "public";
{ TABLE "informix".zdivision row size = 41 number of columns = 3 index size
= 0 }
{ unload file name = zdivi00223.unl number of rows = 609 }
create table "informix".zdivision
(
division varchar(35),
circuit char(2),
district char(3)
);
revoke all on "informix".zdivision from "public";
{ TABLE "informix".zerrdivision row size = 297 number of columns = 4 index
size =
0 }
{ unload file name = zerrd00224.unl number of rows = 14 }
create table "informix".zerrdivision
(
division varchar(35),
circuit char(2),
district char(3),
comments varchar(255)
);
revoke all on "informix".zerrdivision from "public";
{ TABLE "informix".zmasterproject row size = 176 number of columns = 29
index size
= 0 }
{ unload file name = zmast00226.unl number of rows = 148 }
create table "informix".zmasterproject
(
project_number char(9),
circuit char(2),
district char(3),
division varchar(35),
project_type_code char(1),
sqft_total_gross float,
sqft_occupiable float,
design_contractor_code char(3),
num_appeal_courtroom integer,
num_active_dist_judge integer,
num_sr_dist_judge integer,
num_active_appeal_judge integer,
num_sr_appeal_judge integer,
num_dist_courtroom integer,
num_mag_courtroom integer,
num_mag_judge integer,
num_bank_courtroom integer,
num_bank_judge integer,
prosp_dev_study_date date,
design_start_date date,
design_comp_date date,
construct_doc_date date,
contract_award_date date,
occupancy_date date,
design_project_manager_id char(10),
project_manager_id char(10),
last_update_date datetime year to second,
last_update_user_id char(10),
mid_construct_date date
);
revoke all on "informix".zmasterproject from "public";
{ TABLE "informix".zsmproject row size = 191 number of columns = 22 index
size =
0 }
{ unload file name = zsmpr00228.unl number of rows = 0 }
create table "informix".zsmproject
(
lease_effective_date date,
lease_award_date date,
notice_to_proceed_date date,
primary_contacts_id integer,
project_start_date date,
project_title varchar(80),
secondary_contacts_id integer,
solicit_date date,
project_id integer,
contract_comp_date date,
design_comp_date date,
district char(3),
contract_bid_date date,
construct_award_date date,
construct_start_date date,
contract_rev_date date,
gsa_market_survey_date date,
is_district_court integer,
is_bankruptcy_court integer,
is_appellate_court integer,
gsa_project_id varchar(30),
gsa_advertise_date date
);
revoke all on "informix".zsmproject from "public";
{ TABLE "informix".zfutureproject row size = 56 number of columns = 11 index
size
= 0 }
{ unload file name = zfutu00229.unl number of rows = 122 }
create table "informix".zfutureproject
(
plan_score integer,
site_cost money(16,2),
project_type_code char(1),
design_cost money(16,2),
bldg_eng_rpt_date date,
construct_cost money(16,2),
is_auth_status integer,
fy_site char(4),
fy_design char(4),
fy_construct char(4),
is_any_court_model_app integer
);
revoke all on "informix".zfutureproject from "public";
{ TABLE "informix".zcleanupdivision row size = 96 number of columns = 9
index size
= 0 }
{ unload file name = zclea00233.unl number of rows = 15 }
create table "informix".zcleanupdivision
(
id serial not null ,
olddivision varchar(35),
oldbuilding_number char(6),
olddistrict char(3),
division varchar(35),
circuit char(2),
state_code char(2),
district char(3),
division_id integer
);
revoke all on "informix".zcleanupdivision from "public";
{ TABLE "informix".zuniquebuilding row size = 10 number of columns = 2 index
size
= 15 }
{ unload file name = zuniq00236.unl number of rows = 1511 }
create table "informix".zuniquebuilding
(
building_id integer,
building_number char(6)
);
revoke all on "informix".zuniquebuilding from "public";
{ TABLE "informix".tabname_code row size = 36 number of columns = 1 index
size =
0 }
{ unload file name = tabna00251.unl number of rows = 0 }
create table "informix".tabname_code
(
last_update_user_id varchar(35) not null
);
revoke all on "informix".tabname_code from "public";
{ TABLE "informix".zduty_station_code row size = 61 number of columns = 4
index size
= 0 }
{ unload file name = zduty00336.unl number of rows = 719 }
create table "informix".zduty_station_code
(
duty_station_code char(5),
circuit char(2),
district char(3),
duty_station_city_desc varchar(50)
);
revoke all on "informix".zduty_station_code from "public";
{ TABLE "informix".zcleanuser row size = 201 number of columns = 20 index
size =
0 }
{ unload file name = zclea00405.unl number of rows = 12 }
create table "informix".zcleanuser
(
user_id varchar(10) not null ,
password varchar(10) not null ,
first_name varchar(25) not null ,
last_name varchar(25) not null ,
mid_initial char(1),
email_address varchar(50),
phone_number char(10),
phone_extension varchar(6),
create_date datetime year to second
default current year to second not null ,
create_user_id varchar(10) not null ,
last_update_date datetime year to second
default current year to second not null ,
last_update_user_id varchar(10) not null ,
district char(3),
circuit char(2),
court_type_code char(1) not null ,
unit_type_code char(1),
user_privilege integer not null ,
scope_district char(3),
scope_circuit char(2),
is_active integer
default 1
);
revoke all on "informix".zcleanuser from "public";
{ TABLE "informix".zclean_user row size = 205 number of columns = 21 index
size =
0 }
{ unload file name = zclea00410.unl number of rows = 23 }
create table "informix".zclean_user
(
user_id serial not null ,
login_user_id varchar(10) not null ,
password varchar(10) not null ,
first_name varchar(25) not null ,
last_name varchar(25) not null ,
mid_initial char(1),
email_address varchar(50),
phone_number char(10),
phone_extension varchar(6),
create_date datetime year to second
default current year to second not null ,
create_user_id varchar(10) not null ,
last_update_date datetime year to second
default current year to second not null ,
last_update_user_id varchar(10) not null ,
district char(3),
circuit char(2),
court_type_code char(1) not null ,
unit_type_code char(1),
user_privilege integer not null ,
scope_district char(3),
scope_circuit char(2),
is_active integer
default 1
);
revoke all on "informix".zclean_user from "public";
{ TABLE "informix".database_revision row size = 45 number of columns = 5
index size
= 12 }
{ unload file name = datab00476.unl number of rows = 12 }
create table "informix".database_revision
(
id serial not null ,
db_name varchar(20),
db_revision_no float,
last_update_date datetime year to second
default current year to second not null ,
last_update_user_id integer not null ,
primary key (id) constraint "informix".database_revision_id_pk
);
revoke all on "informix".database_revision from "public";
{ TABLE "informix".ztest row size = 2055 number of columns = 2 index size =
0 }
{ unload file name = ztest00571.unl number of rows = 5 }
create table "informix".ztest
(
id integer not null ,
notes "informix".lvarchar
);
revoke all on "informix".ztest from "public";
{ TABLE "informix".ztest2 row size = 9 number of columns = 1 index size =
0 }
{ unload file name = ztest00606.unl number of rows = 1 }
create table "informix".ztest2
(
amt money(16,2)
default 0
);
revoke all on "informix".ztest2 from "public";
{ TABLE "informix".building_import_bkp row size = 233 number of columns = 36
index
size = 0 }
{ unload file name = build00616.unl number of rows = 336 }
create table "informix".building_import_bkp
(
building_number char(6),
building_name varchar(30),
address_gsa varchar(35),
gsa_city varchar(23),
built_date date,
gsa_region_code char(2),
sqft_usable float,
sqft_rentable float,
ru_factor float,
num_inside_parking integer,
num_outside_parking integer,
building_owner_code char(2),
ab_1001 integer,
ab_1002 integer,
ab_1005 integer,
ab_1007 integer,
ab_1011 integer,
ab_1013 integer,
ab_1014 integer,
ab_1016 integer,
ab_1031 integer,
ab_1032 integer,
ab_1033 integer,
ab_1035 integer,
ab_1037 integer,
ab_1041 integer,
ab_1042 integer,
ab_1044 integer,
ab_1046 integer,
ab_1047 integer,
ab_1501 integer,
ab_1525 integer,
ab_1535 integer,
ab_4700 integer,
is_new_building integer
default 0,
file_date date
);
revoke all on "informix".building_import_bkp from "public";
{ TABLE "informix".cbr_import_bk row size = 254 number of columns = 32 index
size
= 0 }
{ unload file name = cbr_i00640.unl number of rows = 10922 }
create table "informix".cbr_import_bk
(
cbr_import_id serial not null ,
cbr_number char(9),
building_number char(6),
ab_code char(4),
agency_bureau_name varchar(20),
cbr_expiration_date date,
cbr_rentable_sqft float,
cbr_usable_sqft float,
ru_factor float,
lease_number varchar(9),
sqft_adp float,
sqft_auditorium float,
sqft_cafeteria float,
sqft_child_care float,
sqft_conference_train float,
sqft_courtroom float,
sqft_fitness_center float,
sqft_food_service float,
sqft_general_storage float,
sqft_health_unit float,
sqft_judge_chambers float,
sqft_judge_hearing float,
sqft_laboratory float,
sqft_other float,
sqft_residential float,
sqft_snack_bar float,
sqft_structure_change float,
sqft_floorcut float,
sqft_office_total float,
sqft_warehouse float,
file_date date,
sqft_light_industrial float
);
revoke all on "informix".cbr_import_bk from "public";
{ TABLE "informix".unit_type_code row size = 39 number of columns = 4 index
size
= 7 }
{ unload file name = unit_00716.unl number of rows = 14 }
create table "informix".unit_type_code
(
unit_type_code char(1) not null ,
unit_type_desc varchar(25),
last_update_date datetime year to second
default current year to second not null ,
last_update_user_id integer not null ,
primary key (unit_type_code) constraint "informix".unit_type_code_pk
);
revoke all on "informix".unit_type_code from "public";
{ TABLE "informix".court_type_code row size = 68 number of columns = 5 index
size
= 7 }
{ unload file name = court00717.unl number of rows = 8 }
create table "informix".court_type_code
(
last_update_date datetime year to second not null ,
court_type_desc varchar(50),
court_type_code char(1) not null ,
last_update_user_id integer not null ,
sort_order integer,
primary key (court_type_code) constraint "informix".court_type_code_pk
);
revoke all on "informix".court_type_code from "public";
{ TABLE "informix".agency_bureau_code row size = 418 number of columns = 11
index
size = 39 }
{ unload file name = agenc00718.unl number of rows = 42 }
create table "informix".agency_bureau_code
(
ab_code char(4) not null ,
last_update_date datetime year to second
default current year to second not null ,
space_desc varchar(255),
lease_term integer,
court_name varchar(50)
default 'UNKNOWN' not null ,
bureau_name varchar(80) not null ,
last_update_user_id integer not null ,
court_type_code char(1),
unit_type_code char(1),
ab_code_rollup char(4),
is_current integer
default 0,
primary key (ab_code) constraint "informix".ab_code_pk
);
revoke all on "informix".agency_bureau_code from "public";
{ TABLE "informix".allotment row size = 100 number of columns = 11 index
size = 63
}
{ unload file name = allot00719.unl number of rows = 124 }
create table "informix".allotment
(
create_date datetime year to second
default current year to second not null ,
create_user_id integer not null ,
sub_boc char(2),
fy char(4) not null ,
description varchar(50),
amount money(16,2)
default $0.00 not null ,
allocation_date date not null ,
allotment_id serial not null ,
boc_code char(4) not null ,
fund_code char(6) not null ,
budget_organization_id integer not null ,
primary key (allotment_id) constraint "informix".allotment_id_pk
);
*** prepare sqlobj
202 - An illegal character has been found in the statement.
Obnoxio The Clown
> Hi all.
>
> I have one machine running IDS 9.21 (?) on Solaris (7?). My objective was
> to do take one of the databases and all its data and put it on another
> machine running IDS 9.4 on Windows XP.
>
> I was seemingly able to grab the data by using dbexport. I got a directory
> with a *.sql file and a whole bunch of *.unl files. I then moved this to
> my Windows machine and tried to use dbimport to recreate the db and its
> information. The process runs for a while, and then stops with an error:
> "202 - An illegal character has been found in the statement."
>
> This is the command I ran in Windows:
> C:\Database\informix_backup>\database\informix\bin\dbimport jfacts -c -i
> c:\database\informix_backup
>
> What can I do to fix this?
>
> The output of the dbimport is as follows, from dbimport.out :
>
> Thanks!
[SNIP]
> { TABLE "informix".allotment row size = 100 number of columns = 11 index
> size = 63
> }
> { unload file name = allot00719.unl number of rows = 124 }
>
> create table "informix".allotment
> (
> create_date datetime year to second
> default current year to second not null ,
> create_user_id integer not null ,
> sub_boc char(2),
> fy char(4) not null ,
> description varchar(50),
> amount money(16,2)
> default $0.00 not null ,
> allocation_date date not null ,
> allotment_id serial not null ,
> boc_code char(4) not null ,
> fund_code char(6) not null ,
> budget_organization_id integer not null ,
> primary key (allotment_id) constraint "informix".allotment_id_pk
> );
> *** prepare sqlobj
> 202 - An illegal character has been found in the statement.
You may need to put quotes around the $0.00. I'd say it's a bug, just from
the above.
--
"C'est pas parce qu'on n'a rien ą dire qu'il faut fermer sa gueule"
- Coluche
Mohamed Marwan
Hi ;
Would u check the CLIENT_LOCALE , DB_LOCALE , DB_DATE of the two servers ,
make the new identical to the old, and try again.
Mohammad Marwan
Senior DBA and Unix administrator
Mediterranean Smart Cards Company
* *********** ****** ******* * *******
* Fax: +20(2) 7621164
* E-mail: mma...@mscc.com.eg
( Mobile: +2 0122449825
sending to informix-list
Daniel
"Obnoxio The Clown" <obn...@hotmail.com> wrote in message
news:bvst48$101pgm$3...@ID-64669.news.uni-berlin.de...
Hello.
I tried "$0.00" and that did not work. However, "0.00" did work. The
dbimport program ran more this time, but did not complete itself. I got a
different error this time, complaining that I ran out of disk space. My HD
still has over 44 gigs of free space left.
[dbimport running...]
revoke all on "informix".module_code from "public";
{ TABLE "informix".notes row size = 2328 number of columns = 7 index size =
43 }
{ unload file name = notes00750.unl number of rows = 7062 }
create table "informix".notes
(
create_date datetime year to second
default current year to second not null ,
create_user_id integer not null ,
subject varchar(255),
notes "informix".lvarchar not null ,
notes_id serial not null ,
module_code char(1) not null ,
subject_id integer not null ,
primary key (notes_id) constraint "informix".notes_id_pk
);
*** put loadcur
271 - Could not insert new row into the table.
131 - ISAM error: no free disk space
Thanks again!
Obnoxio The Clown
Are you sure you're inserting where you think you're inserting...? (Fnaar!)
--
"C'est pas parce qu'on n'a rien à dire qu'il faut fermer sa gueule"
- Coluche
Daniel
I am inserting into rootdbs.
I think that is the problem, as rootdbs is only 50 megs and my new db takes
more space than thtat. I have tried to increase the rootdbs size by changing
the ROOTSIZE variable in etc\onconfig.std and etc\onconfig.mymachinename
files, restarted the server (and rebooted the machine). However, I still see
the file as being 50 megs, and I stillg get the same error of no free disk
space.
Perhaps it's best not to but my new db into rootdbs, but for now, I would
just be happy to get this thing working (dbimport). Is the above the right
procedure to change the rootdbs size?
> --
> "C'est pas parce qu'on n'a rien ą dire qu'il faut fermer sa gueule"
> - Coluche
Neil Truby
No disrespect to you, but you're so lacking in even the basics that it's
unfair that you should be landed with this task. Would your organisation
have given an important accounting task to someone without the necessary
training and experience? I thought not.
Anyway, to answer your question the correct way to increase the size of the
rootdbs is to use tbspace or onspaces (lost track of which version of the
engine you are on) to add a new chunk to it.
regards
Neil
"Daniel" <dhw...@nothing.no.spam.com> wrote in message
news:1qwUb.20466$CJ1.18292@lakeread01...
> > "C'est pas parce qu'on n'a rien à dire qu'il faut fermer sa gueule"
> > - Coluche
>
>
Obnoxio The Clown
No, it isn't. You should really use onspaces to add more dbspaces.
--
"C'est pas parce qu'on n'a rien à dire qu'il faut fermer sa gueule"
- Coluche
Daniel
I do have experience with databases (SQL Server 2000), just not with
Informix.
Anyway, I got it to work by reinstalling the software and by increasing the
rootdbs size.
The dbimport loaded up fine now.
Thanks all!
"Neil Truby" <neil....@ardenta.com> wrote in message
news:bvu6pr$116re9$1...@ID-162943.news.uni-berlin.de...