please provide the syntax for dropping a column and changing the column definitions using alter which works in db2 z/os

please provide the syntax for dropping a column and changing the column definitions using alter which works in db2 z/os.

Also I am running a db2 load utility through jcls and I am getting the following error
DSNUGBAC - RESOURCE UNAVAILABLE
          REASON 00D70014
          TYPE 00000220
          NAME UCDB2P01.DSNDBC.DPMDDG.SPMDDG1.I0001.A001
DSNUGBAC - UTILITY DATA BASE SERVICES MEMORY EXECUTION ABENDED, REASON=X'00E40318' CAUSE=X'00C9000A'.
The abend is SO4E.we tried increasing the primary and sec qty in the tablespace declaration but it failed.
the JCL is as below
//IF1     IF   CHECK1.RC = 0 THEN
//PART1A EXEC   DB21UTLL,UID='PART1',CONDCDE=4
//*        ,UTPROC='RESTART(PHASE)'
//SYSIN    DD  DSN=HLQ1.TEST.CNTL(CNTLCRD),DISP=SHR
//SYSREC   DD  DSN=HLQ1.INPUT.FILE.,DISP=SHR
//SYSDISC  DD  DSN=HLQ1.INPUT.SYSDISC(+1),
//             DISP=(NEW,CATLG,DELETE),
//             UNIT=SYSDA,
//             SPACE=(CYL,(200,200),RLSE),
//             DCB=(DSCB1,RECFM=FB,LRECL=80,BLKSIZE=0)
//SYSPRINT DD  DSN=HLQ1.TEST.SYSPRNT,
//             DISP=(NEW,CATLG,CATLG),
//             UNIT=SYSDA,
//             SPACE=(CYL,(200,200),RLSE),
//             DCB=(DSCB1,RECFM=FB,LRECL=80,BLKSIZE=0)
//SYSMAP   DD  DSN=&&SYSMAPA,
//             DISP=(,DELETE),
//             SPACE=(CYL,(10,10),RLSE),
//             UNIT=SYSDA
//SYSERR   DD  DSN=&&SYSERRA,
//             DISP=(,DELETE),
//             SPACE=(CYL,(10,10),RLSE),
//             UNIT=SYSDA
//SYSUT1   DD  DSN=&&SYSUT1A,
//             DISP=(,DELETE),
//             SPACE=(CYL,(10,10),RLSE),
//             UNIT=SYSDA
//SYSUT2   DD  DSN=&&SYSUT2A,
//             DISP=(,DELETE),
//             SPACE=(CYL,(10,10),RLSE),
//             UNIT=SYSDA
//SORTOUT  DD  DSN=&&SORTOUTA,
//             DISP=(,DELETE),
//             SPACE=(CYL,(10,10),RLSE),
//             UNIT=SYSDA
//*
//END1     ENDIF
//*
TABLE SPACE DEFINITIONS:
CREATE LARGE TABLESPACE TS1  IN DB1
       NUMPARTS 2
       (PARTITION 1    USING STOGROUP SMS1
                             PRIQTY 720
                             SECQTY 72000
                             ERASE NO
                       FREEPAGE 0
                       PCTFREE 10
                       TRACKMOD NO
                       COMPRESS NO
       ,PARTITION 2    USING STOGROUP SMS1
                             PRIQTY 720
                         SECQTY 72000
                      ERASE NO
                FREEPAGE 0
                PCTFREE 10
                TRACKMOD NO
                COMPRESS NO
)
BUFFERPOOL BP3
LOCKSIZE PAGE
      LOCKMAX SYSTEM
CLOSE NO
CCSID EBCDIC;
Please help

Nigelle

NigelleAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

momi_sabagCommented:
hi
which db2 version are you running on zOS ?
you can not drop a column from a table in db2 for zos
if you want to alter a columns data type, your options are limited accroding to the version you are using

reason code 00D70014 means that sms could not find enough free space to allocate your file, which means, you need to make the primary quantity smaller, not bigger,
which shrlevel did you use in your load command ?
0
NigelleAuthor Commented:
shrlevel  used is change.which file did it not find enough space to allocate?also the primary quantity in the space allocation of file or in the table space.Can you please indicate the change in the jcl or tablespace ddl
0
momi_sabagCommented:
the file db2 could not allocate is
UCDB2P01.DSNDBC.DPMDDG.SPMDDG1.I0001.A001
which is the tablespace file for the tablespace DPMDDG.SPMDDG1
i believe this is the tablespace against which you run the load utility
in order to make the primary quantity smaller you should run
alter tablespace DPMDDG.SPMDDG1
 priqty xxx secqty yyy
0
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

NigelleAuthor Commented:
we ran a reorg on the table and it worked fine.But we actually succeeded by increasing the primary qty and it worked. I am not sure if it worked bcoz of the reorg or the increase in priqty
0
momi_sabagCommented:
what worked ? the reorg or the load ?
did you use the reuse parameter on your reorg ?

did you drop other tablespaces between the time you ran the load that failed and the reorg that worked ? did you shrunk other tablespaces ? added more volumes to the sms pool ?
it does not make sense since load and reorg work in the same way
when you run the first load that failed, did you recieve in the xxxxMSTR syslog messages a message indicating a media system happend and db2 could not allocate more extents to the file ?
0
NigelleAuthor Commented:
yes it said the system cannot allocate more extents to the file in the syslog mesages.I am not sure what worked here.I am prettty confused
0
momi_sabagCommented:
ok
this explains everything
when you create a tablespace in db2, you specify 2 size parameters - priqty, secqty
db2 will then allocate a vsam file using those parameters,
the priqty will be the initial size allocated,
once that part gets full db2 will start allocating additional extents using the secqty size you specified
today, you can only have 251 extents for a file (this is an operating system limit, not a db2 limit)
so once you reach 251 extents, db2 will not be able to allocate additional space for your tablespace and you'll get the errors from the media manager regarding unable to allocate more extents

when you perform a reorg or a load, db2 recreates the vsam file that holds the data in the tablespace, and allocate extents as it needs so this is why you got the error, even though you could recieve this error when you are doing a simple insert and the tablespace is full

when you increased your priqty, it db2 longer to fill the first part it has allocated, and thus it needed less extents for the rest of the data, and you did not recieve the error again,
so now, the load should work as well, if you are loading the same amount of data that you currently have in the tablespace, and you are loading with replace

in order to see how much priqty and secqty are for your tablespace, you can run this query
select pqty,sqty
from sysibm.systablepart
where dbname='your_db_name'
and tsnAME='your_ts_name'

but you need to multiply the above values with your tablespace page size (usually 4 unless you specified otherwise when you created it)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NigelleAuthor Commented:
Thanks a million !!!!! You are a genius
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2

From novice to tech pro — start learning today.