RE: Help with SQL script

James

The error messages would be useful, although my guess is it's failing on the first ALTER TABLE / DROP statement as the tables don't already exist.

I'm sure I've seen ways of testing for the existence of a table before doing any form of drop, but can't find the links so you might be advised to comment out those statements with '--' the first time you run the script.

If anybody knows a way of checking the tables existence first please share.

All the best

Jonathan


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of midrange-l-request@xxxxxxxxxxxx
Sent: 15 August 2012 02:15
To: midrange-l@xxxxxxxxxxxx
Subject: MIDRANGE-L Digest, Vol 11, Issue 1236

Send MIDRANGE-L mailing list submissions to
midrange-l@xxxxxxxxxxxx


message: 5
date: Tue, 14 Aug 2012 16:22:53 -0700
from: James Lampert <jamesl@xxxxxxxxxxxxxxxxx>
subject: Help with SQL script

We received an SQL script from one of our off-site developers, an SQL expert who doesn't know scat about AS/400s, one that apparently runs fine on DB2 for Unix/Linux, but failed miserably when I tested it on our
V6 box.

Even if I *could* publish all 938 lines of it, I wouldn't burden the List with it. Instead, a rough summary of the thing, with my abridgments in <angle brackets>:

--==============================================================
-- DBMS name: IBM DB2 UDB 9.7 Common Server
-- Created on: 2012/8/7 0:46:18
--==============================================================


alter table B_User_Roles
drop foreign key F_Relationship_20;

alter table B_User_Roles
drop foreign key F_Relationship_21;

<and so forth, a bunch of "alter table" statements for tables that don't exist yet, which the developer *says* shouldn't stop the script from creating the files from scratch>

drop table B_Groups;

drop table B_Roles;

drop table B_Settings;

drop index Relationship_21_FK;

drop index Relationship_20_FK;

drop table B_User_Roles;

<and so forth, dropping any existing files in the library>

--==============================================================
-- Table: B_Groups
--==============================================================
create table B_Groups
(
group_id NUMERIC(20) not null,
name VARCHAR(50) not null,
description VARCHAR(200),
constraint P_Identifier_1 primary key (group_id) );

--==============================================================
-- Table: B_Roles
--==============================================================
create table B_Roles
(
role_id NUMERIC(20) not null,
name VARCHAR(50) not null,
description VARCHAR(100),
type SMALLINT,
is_grantable SMALLINT,
function_name VARCHAR(50),
object_id NUMERIC(20),
object_type INTEGER,
constraint P_Identifier_1 primary key (role_id) );

--==============================================================
-- Table: B_Settings
--==============================================================
create table B_Settings
(
setting_id NUMERIC(20) not null,
name VARCHAR(50) not null,
content VARCHAR(1024),
owner_type SMALLINT,
owner_id NUMERIC(20),
constraint P_Identifier_1 primary key (setting_id) );

--==============================================================
-- Table: B_User_Roles
--==============================================================
create table B_User_Roles
(
user_id NUMERIC(20),
role_id NUMERIC(20)
);

--==============================================================
-- Index: Relationship_20_FK
--==============================================================
create index Relationship_20_FK on B_User_Roles (
user_id ASC
);

--==============================================================
-- Index: Relationship_21_FK
--==============================================================
create index Relationship_21_FK on B_User_Roles (
role_id ASC
);

<continuing with a bunch of additional CREATE TABLE and CREATE INDEX
statements>

alter table B_User_Roles
add constraint F_Relationship_20 foreign key (user_id)
references B_Users (user_id)
on delete restrict on update restrict;

alter table B_User_Roles
add constraint F_Relationship_21 foreign key (role_id)
references B_Roles (role_id)
on delete restrict on update restrict;

<continuing with a bunch more ALTER TABLE statements>

******

Running the whole script (in RUNSQLSTM) failed, but running the first CREATE TABLE from it, in isolation, worked.

Is there any reasonable expectation that the whole script would even run on an AS/400? I don't even understand the error messages I got.

--
JHHL


------------------------------


Proud partner of The Ageas Bowl and the Ageas Salisbury International Arts Festival.

Registered Address: Ageas House Tollgate Eastleigh Hampshire SO53 3YA Registered Number: 354568 England
Authorised and regulated by the Financial Services Authority

This e-mail together with any attachments are intended for the addressee only and may be private and confidential. If you are not the intended recipient, or the person responsible for delivering it to the intended recipient, you must not open any attachments, or copy, disclose, distribute, retain or use this e-mail, including any attachments, in any way whatsoever; please return it to us immediately using the reply facility on e-mail.

Consider the environment and think before you print this email.