newbie with problems restoring db
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!
{ 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) ,@@
Daniel wrote:
> 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