Re: command/SQL to drop fields

[CRPence <CRPbottle@xxxxxxxxx>]

donna lester wrote:
> I am trying to drop 3 fields in a 15 field physical file. Some
> physical files have key & also logical files, some do not have
> key/logical files. I am using SQL command  ALTER TABLE "PF"  DROP
> "FIELD" -- which allows me to drop only one field at a time.
> If I want to drop 3 fields at one time, what is the SQL/CL
> command I can use on a keyed/nonkeyed PF (with/without logical
> files)?

  As Mark suggested, since presumably what is referred to is a DDS 
PF, modify the DDS for the file, and then issue CHGPF SRCFILE(named) 
SRCMBR(named); where the "named" values refer to the modified DDS 
source member.

  As for the ALTER TABLE statement of SQL, review the syntax 
diagram to see that there is no such limitation of dropping only one 
column:
http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/db2/rbafzatabl.htm 


  See the example below, for which there are not only two columns 
being dropped, but additionally one remaining column is changed to a 
new data type.

 create table qtemp/sf (a int, b int, c int, d int)
 ;
 alter  table qtemp/sf
           drop  column b
           drop  column c
           alter column d set data type decimal(9, 0)
 ;

  As for keyed versus non-keyed PF, that is moot; the access path, 
the entire key, will be removed when a keyed field is removed when 
using SQL ALTER TABLE and desired keys established when using the 
CHGPF SRCFILE().  However if any fields that are removed or altered 
are a key field of any logical files, then there is the DLTDEPLF() 
parameter on CHGPF SRCFILE() to enable the request to complete; be 
prepared to create the logical files again and assign ownership & 
authority.

Regards, Chuck