× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Also, now that I think about it, since this is the first time you have tried
this on this system, the objects in question probably don't already exist.
(?)

That being true, it'll die on the first DROP.

You could get around that by using SEV(40) (or whatever the parm is for
"maximum allowed severity"). But if there are other problems, it may mask
those too.

Safest is to put '-- ' (yes that's two dashes and a space) in front of each
DROP statement for the first execution. -- is the same as a comment.

Dennis Lovelady
http://www.linkedin.com/in/dennislovelady
--
"Get your facts first, and then you can distort them as much as you please."
-- Mark Twain


RUNSQLSTM will output a print file with the error line number and its
description.

WRKSPLF after the failure.

Dennis Lovelady
http://www.linkedin.com/in/dennislovelady
--
REALITY.SYS corrupted. Reboot universe <Y/N>?


since you didn't post the error part - we don't understand it
either....

I can't give you a quick link, but believe IBM has an xref table of
the differences between the different versions of DB2.
Also, as always, make sure you are up to date on the DB2 ptf group.
Jim Franz
----- Original Message -----
From: "James Lampert" <jamesl@xxxxxxxxxxxxxxxxx>
To: "Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>
Sent: Tuesday, August 14, 2012 7:22 PM
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
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please
take a moment to review the archives at
http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at
http://archive.midrange.com/midrange-l.



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.