Focal Point
CAR Database on IBM i (AS/400)

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/6057093516

January 13, 2012, 11:21 AM
K2R400
CAR Database on IBM i (AS/400)
Hello,

I'm new on this forum and with webfocus.
Does somebody use wf on an IBM i (DB2 Web Query) ?
If yes, how to configure the car database on IBM i ?
Or perhaps someone has an SQL Script for the CAR's Database ?

Thank you in advance.


WebFocus 7.7 (DB2 Web Query 1.1.2). IBM i
January 13, 2012, 06:08 PM
Mighty Max
Do you have the application folder ibisamp?
C:\ibi\apps\ibisamp. This is where all the sample focus files are located. There is a fex procedure named loadcar.fex. If you run this fex it should create the car focus file.


WebFOCUS 8.1.05M Unix Self-Service/MRE/Report Caster - Outputs Excel, PDF, HTML, Flat Files
January 16, 2012, 03:09 AM
K2R400
Thanks for your response.

I can see the loadcar.fex here : /qibm/proddata/webQuery/ibi/srv77/home/etc/apps/ibisamp/loadcar.fex

I can see the mas file :
/qibm/proddata/webQuery/ibi/srv77/home/etc/apps/ibisamp/car.mas

but it's not possible to use them on an IBM i.
Somebody else have an IBM i (AS/400 ou i5) ?


WebFocus 7.7 (DB2 Web Query 1.1.2). IBM i
January 16, 2012, 08:57 AM
GamP
WebQuery - isn't that a custom version of webfocus - with a lot less functionality?
Could be you can indeed not use the car database in the standard (focus db) form.
Could be that WebQuery is DB2 only.
Check with IBI.


GamP
- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
January 19, 2012, 09:35 AM
K2R400
quote:
WebQuery - isn't that a custom version of webfocus - with a lot less functionality?

Ye i think so.

quote:

Could be you can indeed not use the car database in the standard (focus db) form.
Could be that WebQuery is DB2 only.

DB2 Web Query can work with SQL Server too.

Below an SQL script for DB2 Web Query user's :

   
--------------------------------------------------------------------
-- Special thanks to Francis Mariani
--------------------------------------------------------------------
CREATE TABLE QGPL.SQLCAR_ORIGIN ( 
	COUNTRY CHAR(10)  	DEFAULT NULL , 
	CONSTRAINT QGPL.PK_SQLCAR_ORIGIN PRIMARY KEY( COUNTRY ) ) ; 

INSERT INTO qgpl.SQLCAR_ORIGIN (COUNTRY) 
VALUES	('ENGLAND'), 
	('JAPAN'), 
	('ITALY'), 
	('W GERMANY'), 
	('FRANCE'); 
--------------------------------------------------------------------
CREATE TABLE QGPL.SQLCAR_COMP ( 
	COUNTRY 	CHAR(10)  	DEFAULT NULL , 
	CAR 		CHAR(16)	DEFAULT NULL , 
	CONSTRAINT QGPL.PK_SQLCAR_COMP PRIMARY KEY( COUNTRY , CAR ) ) ; 
  
ALTER TABLE QGPL.SQLCAR_COMP 
	ADD CONSTRAINT QGPL.FK_SQLCAR_COMP_ORIGIN 
	FOREIGN KEY( COUNTRY ) 
	REFERENCES QGPL.SQLCAR_ORIGIN ( COUNTRY ) 
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION ; 

INSERT INTO qgpl.SQLCAR_COMP (COUNTRY, CAR) 
VALUES 	('ENGLAND', 	'JAGUAR'),
	('ENGLAND', 	'JENSEN'),
	('ENGLAND', 	'TRIUMPH'),
	('JAPAN', 	'DATSUN'),
	('JAPAN', 	'TOYOTA'),
	('ITALY', 	'MASERATI'),
	('ITALY', 	'ALFA ROMEO'),
	('W GERMANY', 	'AUDI'),
	('W GERMANY', 	'BMW'),
	('FRANCE', 	'PEUGEOT');
--------------------------------------------------------------------
CREATE TABLE QGPL.SQLCAR_CARREC ( 
	COUNTRY 	CHAR(10)  	DEFAULT NULL , 
	CAR 		CHAR(16)	DEFAULT NULL , 
	MODEL 		CHAR(24)  	DEFAULT NULL , 
	CONSTRAINT QGPL.PK_SQLCAR_CARREC PRIMARY KEY( COUNTRY , CAR , MODEL ) ) ; 
  
ALTER TABLE QGPL.SQLCAR_CARREC 
	ADD CONSTRAINT QGPL.FK_SQLCAR_CARREC_COMP 
	FOREIGN KEY( COUNTRY , CAR ) 
	REFERENCES QGPL.SQLCAR_COMP ( COUNTRY , CAR ) 
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION ; 

INSERT INTO qgpl.SQLCAR_CARREC (COUNTRY, CAR, MODEL) 
VALUES 	('ENGLAND', 	'JAGUAR',	'V12XKE AUTO'),
	('ENGLAND', 	'JAGUAR',	'XJ12L AUTO'),
	('ENGLAND', 	'JENSEN',	'INTERCEPTOR III'),
	('ENGLAND', 	'TRIUMPH',	'TR7'),
	('ENGLAND', 	'TRIUMPH',	'2000 SPIDER VELOCE'),
	('ENGLAND', 	'TRIUMPH',	'2000 GT VELOCE'),
	('JAPAN', 	'DATSUN',	'B210 2 DOOR AUTO'),
	('JAPAN', 	'TOYOTA',	'COROLLA 4 DOOR DIX AUTO'),
	('ITALY', 	'MASERATI',	'DORA 2 DOOR'),
	('ITALY', 	'ALFA ROMEO',	'2000 4 DOOR BERLINA'),
	('W GERMANY', 	'AUDI',		'100 LS 2 DOOR AUTO'),
	('W GERMANY', 	'BMW',		'2002 2 DOOR'),
	('W GERMANY', 	'BMW',		'2002 2 DOOR AUTO'),
	('W GERMANY', 	'BMW',		'530I 4 DOOR'),
	('W GERMANY', 	'BMW',		'530I 4 DOOR AUTO'),
	('W GERMANY', 	'BMW',		'3.0 SI 4 DOOR'),
	('W GERMANY', 	'BMW',		'3.0 SI 4 DOOR AUTO'),
	('FRANCE', 	'PEUGEOT',	'504 4 DOOR');


--------------------------------------------------------------------
CREATE TABLE QGPL.SQLCAR_WARANT ( 
	COUNTRY 	CHAR(10)  	DEFAULT NULL , 
	CAR 		CHAR(16)	DEFAULT NULL , 
	WARRANTY 	CHAR(40)  	DEFAULT NULL , 
	CONSTRAINT QGPL.PK_SQLCAR_WARRANT PRIMARY KEY( WARRANTY , CAR , COUNTRY ) ) ; 
  
ALTER TABLE QGPL.SQLCAR_WARANT 
	ADD CONSTRAINT QGPL.FK_SQLCAR_WARRANT_COMP 
	FOREIGN KEY( COUNTRY , CAR ) 
	REFERENCES QGPL.SQLCAR_COMP ( COUNTRY , CAR ) 
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION ;

INSERT INTO qgpl.SQLCAR_WARANT (COUNTRY, CAR, WARRANTY)
VALUES 	('ENGLAND', 	'JAGUAR',	'12 MONTHS OR 12000 MILES'),
	('ENGLAND', 	'JENSEN',	'12000 MILES OR 12 MONTHS'),
	('ENGLAND', 	'TRIUMPH',	'12 MONTHS OR 12000 MILES'),
	('JAPAN', 	'DATSUN',	'12 MONTHS OR 12000 MILES'),
	('JAPAN', 	'TOYOTA',	'12 MONTHS OR 12500 MILES'),
	('ITALY', 	'MASERATI',	'6 MONTHS OR 6000 MILES'),
	('W GERMANY', 	'AUDI',		'12 MONTHS OR 20000 MILES'),
	('FRANCE', 	'PEUGEOT',	'12 MONTHS ON 12000 MILES');
--------------------------------------------------------------------
CREATE TABLE QGPL.SQLCAR_EQUIP ( 
	COUNTRY CHAR(10)  	DEFAULT NULL , 
	CAR CHAR(16)  		DEFAULT NULL , 
	STANDARD CHAR(40)  	DEFAULT NULL , 
	CONSTRAINT QGPL.PK_SQLCAR_EQUIP PRIMARY KEY( COUNTRY , CAR , STANDARD ) ) ; 
  
ALTER TABLE QGPL.SQLCAR_EQUIP 
	ADD CONSTRAINT QGPL.FK_SQLCAR_EQUIP_COMP 
	FOREIGN KEY( COUNTRY , CAR ) 
	REFERENCES QGPL.SQLCAR_COMP ( COUNTRY , CAR ) 
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION ; 
  

INSERT INTO qgpl.SQLCAR_EQUIP (COUNTRY, CAR, STANDARD)
VALUES 	('ENGLAND', 	'JAGUAR',	'POWER STEERING'),
	('ENGLAND', 	'JAGUAR',	'4 WHEEL DISC BRAKES'),
	('ENGLAND', 	'JAGUAR',	'WHITEWALL RADIAL PLY TIRES'),
	('ENGLAND', 	'JAGUAR',	'WRAP AROUND BUMPERS'),
	('ENGLAND', 	'JAGUAR',	'RECLINING BUCKET SEATS'),
	('ENGLAND', 	'JENSEN',	'AIR CONDITIONING'),
	('ENGLAND', 	'JENSEN',	'LEAR JET AM/FM 8 TRK STEREO'),
	('ENGLAND', 	'JENSEN',	'PIRELLI GR70 15 TIRES'),
	('ENGLAND', 	'JENSEN',	'CHRYSLER 383 CU IN V8 ENGINE'),
	('JAPAN', 	'DATSUN',	'POWER FRONT DISC BRAKES'),
	('JAPAN', 	'DATSUN',	'REAR DRUM BRAKES'),
	('JAPAN', 	'TOYOTA',	'BODY SIDE MOLDING'),
	('JAPAN', 	'TOYOTA',	'MACPHERSON STRUT FROMT SUSPENSION'),
	('ITALY', 	'MASERATI',	'ALL STEEL BODY'),
	('ITALY', 	'MASERATI',	'5 LITRE SS ENGINE'),
	('ITALY', 	'MASERATI',	'MICHELIN 21 5/70 VR15XVWX TIRES'),
	('ITALY', 	'MASERATI',	'CAMPAGNOLLO LIGHT ALLOY  WHEELS'),
	('W GERMANY', 	'AUDI',		'POWER FRONT BRAKES'),
	('W GERMANY', 	'AUDI',		'165 SR 14 RADIAL TIRES'),
	('W GERMANY', 	'AUDI',		'FRONT WHEEL DRIVE'),
	('ENGLAND', 	'TRIUMPH',	'POWER FRONT DISC BRAKES'),
	('ENGLAND', 	'TRIUMPH',	'RETRACTABLE HEADLIGHTS'),
	('FRANCE', 	'PEUGEOT',	'RACK AND PINION STEERING'),
	('FRANCE', 	'PEUGEOT',	'FOUR WHEEL SUSPENSION'),
	('FRANCE', 	'PEUGEOT',	'ANTI SWAY BARS FRONT AND REAR');

--------------------------------------------------------------------
CREATE TABLE QGPL.SQLCAR_BODY ( 
	COUNTRY 	CHAR(10)  	DEFAULT NULL , 
	CAR 		CHAR(16)	DEFAULT NULL , 
	MODEL 		CHAR(24)  	DEFAULT NULL , 
	BODYTYPE	CHAR(12)  	DEFAULT NULL , 
	SEATS 		NUMERIC(4, 0) 	DEFAULT NULL , 
	DCOST 		NUMERIC(8, 0) 	DEFAULT NULL , 
	RCOST 		NUMERIC(8, 0) 	DEFAULT NULL , 
	SALES 		NUMERIC(8, 0) 	DEFAULT NULL , 
	CONSTRAINT QGPL.PK_SQLCAR_BODY PRIMARY KEY( COUNTRY , CAR , MODEL , BODYTYPE ) ) ; 
  
ALTER TABLE QGPL.SQLCAR_BODY 
	ADD CONSTRAINT QGPL.FK_SQLCAR_BODY_CARREC 
	FOREIGN KEY( COUNTRY , CAR , MODEL ) 
	REFERENCES QGPL.SQLCAR_CARREC ( COUNTRY , CAR , MODEL ) 
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION ; 

INSERT INTO qgpl.SQLCAR_BODY 
(COUNTRY, CAR, MODEL, BODYTYPE, SEATS, DCOST, RCOST, SALES) 

VALUES 	('ENGLAND', 'JAGUAR', 'V12XKE AUTO', 'CONVERTIBLE', 2, 7427, 8878, 0),
	('ENGLAND', 'JAGUAR', 'XJ12L AUTO', 'SEDAN', 5, 11194, 13491, 0),
	('JAPAN', 'DATSUN', 'B210 2 DOOR AUTO', 'SEDAN', 4, 2626, 3139, 43000),
	('JAPAN', 'TOYOTA', 'COROLLA 4 DOOR DIX AUTO', 'SEDAN', 4, 2886, 3339, 35030),
	('W GERMANY', 'AUDI', '100 LS 2 DOOR AUTO', 'SEDAN', 5, 5063, 5970, 7800),
	('ITALY', 'ALFA ROMEO', '2000 4 DOOR BERLINA', 'SEDAN', 4, 4915, 5925, 4800),
	('W GERMANY', 'BMW', '2002 2 DOOR', 'SEDAN', 5, 5800, 5940, 8950),
	('W GERMANY', 'BMW', '2002 2 DOOR AUTO', 'SEDAN', 4, 6000, 6355, 8900),
	('W GERMANY', 'BMW', '530I 4 DOOR', 'SEDAN', 5, 8300, 9097, 14000),
	('W GERMANY', 'BMW', '530I 4 DOOR AUTO', 'SEDAN', 5, 8400, 9495, 15600),
	('W GERMANY', 'BMW', '3.0 SI 4 DOOR', 'SEDAN', 5, 10000, 13752, 14000),
	('W GERMANY', 'BMW', '3.0 SI 4 DOOR AUTO', 'SEDAN', 5, 11000, 14123, 18940),
	('FRANCE', 'PEUGEOT', '504 4 DOOR', 'SEDAN', 5, 4631, 5610,0),
	('ENGLAND', 'JENSEN', 'INTERCEPTOR III', 'SEDAN', 4, 14940, 17850, DEFAULT),
	('ITALY', 'MASERATI', 'DORA 2 DOOR', 'COUPE', 2, 25000, 31500, DEFAULT),
	('ENGLAND', 'TRIUMPH', 'TR7', 'HARDTOP', 2, 4292, 5100, DEFAULT),
	('ENGLAND', 'TRIUMPH', '2000 SPIDER VELOCE', 'ROADSTER', 2, 5660, 6820, DEFAULT),
	('ENGLAND', 'TRIUMPH', '2000 GT VELOCE', 'COUPE', 2, 5660, 6820, DEFAULT);

--------------------------------------------------------------------
CREATE TABLE QGPL.SQLCAR_SPECS ( 
	COUNTRY 	CHAR(10)  	DEFAULT NULL , 
	CAR 		CHAR(16)  	DEFAULT NULL , 
	MODEL 		CHAR(24)  	DEFAULT NULL , 
	BODYTYPE 	CHAR(12)  	DEFAULT NULL , 
	LENGTH 		NUMERIC(6, 0) 	DEFAULT NULL , 
	WIDTH 		NUMERIC(6, 0) 	DEFAULT NULL , 
	HEIGHT 		NUMERIC(6, 0) 	DEFAULT NULL , 
	WEIGHT 		NUMERIC(6, 0) 	DEFAULT NULL , 
	WHEELBASE 	NUMERIC(6, 1) 	DEFAULT NULL , 
	FUEL_CAP 	NUMERIC(6, 1) 	DEFAULT NULL , 
	BHP 		NUMERIC(6, 0) 	DEFAULT NULL , 
	RPM 		NUMERIC(6, 0) 	DEFAULT NULL , 
	MPG 		NUMERIC(6, 0) 	DEFAULT NULL , 
	ACCEL 		NUMERIC(6, 0) 	DEFAULT NULL , 
	CONSTRAINT QGPL.PK_SQLCAR_SPECS PRIMARY KEY( COUNTRY , CAR , MODEL , BODYTYPE ) ) ; 
  
ALTER TABLE QGPL.SQLCAR_SPECS 
	ADD CONSTRAINT QGPL.FK_SQLCAR_SPECS_CARREC 
	FOREIGN KEY( COUNTRY , CAR , MODEL ) 
	REFERENCES QGPL.SQLCAR_CARREC ( COUNTRY , CAR , MODEL ) 
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION ; 
  
INSERT INTO qgpl.SQLCAR_SPECS 
(COUNTRY, CAR, MODEL, BODYTYPE, 
LENGTH, WIDTH, HEIGHT, WEIGHT, WHEELBASE, FUEL_CAP, BHP, RPM, MPG, ACCEL)
VALUES 	('ENGLAND', 'JAGUAR', 'V12XKE AUTO', 'CONVERTIBLE', 
		189.6, 66, 48, 3435, 105, 18, 241, 5750, 16, 6.8),
	('ENGLAND', 'JAGUAR', 'XJ12L AUTO', 'SEDAN', 
		198.8, 69.7, 54.1, 4200, 112.8, 24, 241, 5750, 9, 8.5),
	('ENGLAND', 'JENSEN', 'INTERCEPTOR III', 'SEDAN',
		188, 69, 53, 4000, 105, 24, 385, 4700, 11, 7.5),
	('JAPAN', 'DATSUN', 'B210 2 DOOR AUTO', 'SEDAN', 
		163, 60.8, 53.5, 2050, 92.1, 11.6, 70, 6000, DEFAULT,33),
	('ITALY', 'MASERATI', 'DORA 2 DOOR', 'COUPE', 
		177, 69.6, 44.6, 3700, 102.3, 25, 315, 5000, DEFAULT,6),
	('JAPAN', 'TOYOTA', 'COROLLA 4 DOOR DIX AUTO', 'SEDAN', 
		165.2, 61.8, 54.5, 2219, 93.3, 13.2, 75, 5800, 27, DEFAULT),
	('W GERMANY', 'AUDI', '100 LS 2 DOOR AUTO', 'SEDAN', 
		187.2, 69, 54.6, 2571, 105.3, 15.3, 95, 5500, 23, 13),
	('ENGLAND', 'TRIUMPH', 'TR7', 'HARDTOP', 
		164.5, 66.2, 49.9, 2241, 85, 14.5, 90, 5000,25, DEFAULT),
	('ENGLAND', 'TRIUMPH', '2000 SPIDER VELOCE', 'ROADSTER', 
		169.9, 64.2, 50.8, 2320, 88.6, 13.4, DEFAULT, DEFAULT, 21, DEFAULT),
	('ENGLAND', 'TRIUMPH', '2000 GT VELOCE', 'COUPE', 
		163.4, 62.2, 51.8, 2305, 92.5, 14, DEFAULT, DEFAULT, 21, DEFAULT),
	('ITALY', 'ALFA ROMEO', '2000 4 DOOR BERLINA', 'SEDAN', 
		176.7, 61.6, 56.3, 2590, 101, 14, DEFAULT, DEFAULT, 21, DEFAULT),
	('W GERMANY', 'BMW', '2002 2 DOOR', 'SEDAN', 
		176, 62.5, 55.5, 2400, 98.4, 13, DEFAULT, DEFAULT, 24.2, DEFAULT),
	('W GERMANY', 'BMW', '2002 2 DOOR AUTO', 'SEDAN', 
		176, 62.6, 55.5, 2400, 98.4, 12, DEFAULT, DEFAULT, 24.2, DEFAULT),
	('W GERMANY', 'BMW', '530I 4 DOOR', 'SEDAN', 
		189.9, 67.2, 55.9, 3250, 103.8, 18.5, DEFAULT, DEFAULT, 18, DEFAULT),
	('W GERMANY', 'BMW', '530I 4 DOOR AUTO', 'SEDAN', 
		189.9, 67.2, 55.9, 3250, 103.8, 18.5, DEFAULT, DEFAULT, 18, DEFAULT),
	('W GERMANY', 'BMW', '3.0 SI 4 DOOR', 'SEDAN', 
		195, 68.9, 57.1, DEFAULT, 106, 20.6, DEFAULT, DEFAULT, 18, DEFAULT),
	('W GERMANY', 'BMW', '3.0 SI 4 DOOR AUTO', 'SEDAN', 
		195, 68.9, 57.1, DEFAULT, 106, 20.6, DEFAULT, DEFAULT, 18, DEFAULT),
	('FRANCE', 'PEUGEOT', '504 4 DOOR', 'SEDAN', 
		182.4, 66.7, 57, 2860, 108, 14.8, DEFAULT, DEFAULT, 21, DEFAULT);



WebFocus 7.7 (DB2 Web Query 1.1.2). IBM i