Sysprocbody is full - how to get more space???

On May 15, 7:17 pm, "Bret Schuhmacher"
wrote:
> Hello all - I'm new to Informix and I can't find an answer to this simple
> question:
>
> I'm trying to add stored procedures and I'm getting this error:
>
> 312: Cannot update system catalog (sysprocbody).
> 131: ISAM error: no free disk space

So, you need to add more space to the dbspace in which you created the
database where you're failing to a new procedure - most likely, that's
the root dbspace (probably called rootdbs), but it isn't certain. You
will also run into problems adding new data to the database unless the
tables are located in a separate dbspace.

> What I've read says I need to add more space, so I've run the following:
>
> onspaces -c -d test_poc_dat -p c:\IFMXDATA\test_poc\test_poc_dat.002 -s
> 10240 -o 0
>
> and
>
> onspaces -c -d rootdbs_dat -p c:\IFMXDATA\test_poc\rootdbs_dat.002 -s 10240
> -o 0

Those create new dbspaces.

> I'm still getting the error. Was I supposed to add the space to a different
> dbspace? I don't have any more (I don't think). Was I supposed to add a
> chunk instead or something? Do I have to add the dbspace to the oncfg file
> or something?

You need to add an extra chunk to the root dbspace. You also need to
do an archive after adding the chunk - if the data in the system will
matter. If it is a throwaway instance (are you sure?), you can
archive to /dev/null (NUL: on Windows).

Consider dropping the two new chunks since you probably don't need
them any more.

Then use 'onspaces -a' to add the chunk.

On May 15, 7:17 pm, "Bret Schuhmacher"
wrote:
> Hello all - I'm new to Informix and I can't find an answer to this simple
> question:
>
> I'm trying to add stored procedures and I'm getting this error:
>
> 312: Cannot update system catalog (sysprocbody).
> 131: ISAM error: no free disk space

So, you need to add more space to the dbspace in which you created the
database where you're failing to a new procedure - most likely, that's
the root dbspace (probably called rootdbs), but it isn't certain. You
will also run into problems adding new data to the database unless the
tables are located in a separate dbspace.

> What I've read says I need to add more space, so I've run the following:
>
> onspaces -c -d test_poc_dat -p c:\IFMXDATA\test_poc\test_poc_dat.002 -s
> 10240 -o 0
>
> and
>
> onspaces -c -d rootdbs_dat -p c:\IFMXDATA\test_poc\rootdbs_dat.002 -s 10240
> -o 0

Those create new dbspaces.

> I'm still getting the error. Was I supposed to add the space to a different
> dbspace? I don't have any more (I don't think). Was I supposed to add a
> chunk instead or something? Do I have to add the dbspace to the oncfg file
> or something?

You need to add an extra chunk to the root dbspace. You also need to
do an archive after adding the chunk - if the data in the system will
matter. If it is a throwaway instance (are you sure?), you can
archive to /dev/null (NUL: on Windows).

Consider dropping the two new chunks since you probably don't need
them any more.

Then use 'onspaces -a' to add the chunk.

Thanks very much, Jonathan! I think I've done what you recommended, but I'm
still getting the same error. I did the following:

onspaces -a rootdbs_dat -p c:\IFMXDATA\test_poc\rootdbs_dat.003 -s 10240 -o
0
onspaces -a test_poc_dat -p c:\IFMXDATA\test_poc\test_poc_dat.003 -s 10240
-o 0

onbar -b -L 0 -w

That should've done a whole system level 0 archive, right?

I restarted IDS (couldn't get in with dbaccess after all that)

tried to add my storedproc in dbaccess, got the same error:
312: Cannot update system catalog (sysprocbody).
131: ISAM error: no free disk space


I'm pretty sure the rootdbs is what I want to augment - I have the following
in my oncfg.test_poc
ROOTNAME rootdbs # Root dbspace name
ROOTPATH C:\IFMXDATA\test_poc\rootdbs_dat.000 # Path for device containing
root dbspace
ROOTOFFSET 0 # Offset of root dbspace into device (Kbytes)
ROOTSIZE 64000 # Size of root dbspace (Kbytes)


Thanks for your patience helping out a newbie :-).

Rgds,

Bret

Thanks very much, Jonathan! I think I've done what you recommended, but I'm
still getting the same error. I did the following:

onspaces -a rootdbs_dat -p c:\IFMXDATA\test_poc\rootdbs_dat.003 -s 10240 -o
0
onspaces -a test_poc_dat -p c:\IFMXDATA\test_poc\test_poc_dat.003 -s 10240
-o 0

onbar -b -L 0 -w

That should've done a whole system level 0 archive, right?

I restarted IDS (couldn't get in with dbaccess after all that)

tried to add my storedproc in dbaccess, got the same error:
312: Cannot update system catalog (sysprocbody).
131: ISAM error: no free disk space


I'm pretty sure the rootdbs is what I want to augment - I have the following
in my oncfg.test_poc
ROOTNAME rootdbs # Root dbspace name
ROOTPATH C:\IFMXDATA\test_poc\rootdbs_dat.000 # Path for device containing
root dbspace
ROOTOFFSET 0 # Offset of root dbspace into device (Kbytes)
ROOTSIZE 64000 # Size of root dbspace (Kbytes)


Thanks for your patience helping out a newbie :-).

Rgds,

Bret

Bret Schuhmacher wrote:
> Thanks very much, Jonathan! I think I've done what you recommended, but I'm
> still getting the same error. I did the following:

Please post the following:

onstat -d

Output from the following select:

dbaccess sysmaster - < select dbsname, tabname, st.partnum, hex(st.partnum), nextns,
name as dbspace
from systabnames st, sysptnhdr sp, sysdbspaces sd
where st.partnum = sp.partnum
and sd.dbsnum = (st.partnum / 1048576)
and dbsname = 'your database name'
and tabname = 'sysprocbody'
;
EOF

Art S. Kagel

> onspaces -a rootdbs_dat -p c:\IFMXDATA\test_poc\rootdbs_dat.003 -s 10240 -o
> 0
> onspaces -a test_poc_dat -p c:\IFMXDATA\test_poc\test_poc_dat.003 -s 10240
> -o 0
>
> onbar -b -L 0 -w
>
> That should've done a whole system level 0 archive, right?
>
> I restarted IDS (couldn't get in with dbaccess after all that)
>
> tried to add my storedproc in dbaccess, got the same error:
> 312: Cannot update system catalog (sysprocbody).
> 131: ISAM error: no free disk space
>
>
> I'm pretty sure the rootdbs is what I want to augment - I have the following
> in my oncfg.test_poc
> ROOTNAME rootdbs # Root dbspace name
> ROOTPATH C:\IFMXDATA\test_poc\rootdbs_dat.000 # Path for device containing
> root dbspace
> ROOTOFFSET 0 # Offset of root dbspace into device (Kbytes)
> ROOTSIZE 64000 # Size of root dbspace (Kbytes)
>
>
> Thanks for your patience helping out a newbie :-).
>
> Rgds,
>
> Bret

Bret Schuhmacher wrote:
> Thanks very much, Jonathan! I think I've done what you recommended, but I'm
> still getting the same error. I did the following:

Please post the following:

onstat -d

Output from the following select:

dbaccess sysmaster - < select dbsname, tabname, st.partnum, hex(st.partnum), nextns,
name as dbspace
from systabnames st, sysptnhdr sp, sysdbspaces sd
where st.partnum = sp.partnum
and sd.dbsnum = (st.partnum / 1048576)
and dbsname = 'your database name'
and tabname = 'sysprocbody'
;
EOF

Art S. Kagel

> onspaces -a rootdbs_dat -p c:\IFMXDATA\test_poc\rootdbs_dat.003 -s 10240 -o
> 0
> onspaces -a test_poc_dat -p c:\IFMXDATA\test_poc\test_poc_dat.003 -s 10240
> -o 0
>
> onbar -b -L 0 -w
>
> That should've done a whole system level 0 archive, right?
>
> I restarted IDS (couldn't get in with dbaccess after all that)
>
> tried to add my storedproc in dbaccess, got the same error:
> 312: Cannot update system catalog (sysprocbody).
> 131: ISAM error: no free disk space
>
>
> I'm pretty sure the rootdbs is what I want to augment - I have the following
> in my oncfg.test_poc
> ROOTNAME rootdbs # Root dbspace name
> ROOTPATH C:\IFMXDATA\test_poc\rootdbs_dat.000 # Path for device containing
> root dbspace
> ROOTOFFSET 0 # Offset of root dbspace into device (Kbytes)
> ROOTSIZE 64000 # Size of root dbspace (Kbytes)
>
>
> Thanks for your patience helping out a newbie :-).
>
> Rgds,
>
> Bret

Here's onstat -d output:

IBM Informix Dynamic Server Version 10.00.TC6TL -- On-Line -- Up 00:57:17
-- 21568 Kbytes

Dbspaces
address number flags fchunk nchunks pgsize flags owner
name
0C6297E8 1 0x60002 1 2 4096 M B informix
rootdbs
0C71C8B0 2 0x60002 2 1 4096 M B informix
wegmans_poc
0C71CA10 3 0x68002 3 1 4096 M SB informix
sbspace
0C71CB70 4 0x60005 4 1 4096 ND B informix
rootdbs2
0C71CCD0 5 0x60001 6 2 4096 N B informix
wegmans_poc_dat
0C71CE30 6 0x60001 7 2 4096 N B informix
rootdbs_dat
6 active, 2047 maximum

Chunks
address chunk/dbs offset size free bpages flags
pathname
0C629948 1 1 0 12800 6594 PO-B
C:\IFMXDATA\wegmans_poc\rootdbs_dat.000
0C629AC8 1 1 0 12800 0 MD-B
E:\IFMXDATA\wegmans_poc\rootdbs_mirr.000
0C72D6A0 2 2 0 12800 3 PO-B
C:\IFMXDATA\wegmans_poc\wegmans_poc_dat.000
0C71C430 2 2 0 12800 0 MD-B
E:\IFMXDATA\wegmans_poc\wegmans_poc_mirr.000
0C72D820 3 3 0 12800 11873 11879 POSB
C:\IFMXDATA\wegmans_poc\sbspace_dat.000
Metadata 868 554 868
0C71C5B0 3 3 0 12800 0 MDSB
E:\IFMXDATA\wegmans_poc\sbspace_mirr.000
0C72D9A0 4 4 0 12500 0 PD-B
\\.\C:\ifmxdata\wegmans_poc\rootdbs_dat.002
0C72DB20 5 1 0 12500 12497 PO-B
\\.\C:\IFMXDATA\wegmans_poc\wegmans_poc_dat.001
0C71C730 5 1 0 12500 0 MO-B \\.\C:
0C72DCA0 6 5 0 2560 2507 PO-B
c:\IFMXDATA\wegmans_poc\wegmans_poc_dat.002
0C72DE20 7 6 0 2560 2507 PO-B
c:\IFMXDATA\wegmans_poc\rootdbs_dat.002
0C629C90 8 6 0 2560 2557 PO-B
c:\IFMXDATA\wegmans_poc\rootdbs_dat.003
0C629E10 9 5 0 2560 2557 PO-B
c:\IFMXDATA\wegmans_poc\wegmans_poc_dat.003
9 active, 32766 maximum

NOTE: The values in the "size" and "free" columns for DBspace chunks are
displayed in terms of "pgsize" of the DBspace to which they belong.

Expanded chunk capacity mode: always




I had trouble with the query, so I pared it down to retrieve more results
(i.e. removed the where clause)
select dbsname, tabname, st.partnum, hex(st.partnum), nextns,
name as dbspace
from systabnames st, sysptnhdr sp, sysdbspaces sd
where st.partnum = sp.partnum
and sd.dbsnum = (st.partnum / 1048576)

Here's the query output:
no rows



At this point I'm feeling pretty hosed. I don't need *ANYTHING* in my
instance. What's the easiest way to remove EVERYTHING and add a new dbspace
to get going again? Can I use the onspaces -d to remove each dbspace, then
create a dbspace and start over? I'm sure there are many system stored
procedures that I don't want to get rid of... would it be easier to
completely remove Informix, delete IFMXDATA and reinstall? This is a test
instance I set up to try something and it's taking way too long :-(.

Thanks for your assistance!

Here's onstat -d output:

IBM Informix Dynamic Server Version 10.00.TC6TL -- On-Line -- Up 00:57:17
-- 21568 Kbytes

Dbspaces
address number flags fchunk nchunks pgsize flags owner
name
0C6297E8 1 0x60002 1 2 4096 M B informix
rootdbs
0C71C8B0 2 0x60002 2 1 4096 M B informix
wegmans_poc
0C71CA10 3 0x68002 3 1 4096 M SB informix
sbspace
0C71CB70 4 0x60005 4 1 4096 ND B informix
rootdbs2
0C71CCD0 5 0x60001 6 2 4096 N B informix
wegmans_poc_dat
0C71CE30 6 0x60001 7 2 4096 N B informix
rootdbs_dat
6 active, 2047 maximum

Chunks
address chunk/dbs offset size free bpages flags
pathname
0C629948 1 1 0 12800 6594 PO-B
C:\IFMXDATA\wegmans_poc\rootdbs_dat.000
0C629AC8 1 1 0 12800 0 MD-B
E:\IFMXDATA\wegmans_poc\rootdbs_mirr.000
0C72D6A0 2 2 0 12800 3 PO-B
C:\IFMXDATA\wegmans_poc\wegmans_poc_dat.000
0C71C430 2 2 0 12800 0 MD-B
E:\IFMXDATA\wegmans_poc\wegmans_poc_mirr.000
0C72D820 3 3 0 12800 11873 11879 POSB
C:\IFMXDATA\wegmans_poc\sbspace_dat.000
Metadata 868 554 868
0C71C5B0 3 3 0 12800 0 MDSB
E:\IFMXDATA\wegmans_poc\sbspace_mirr.000
0C72D9A0 4 4 0 12500 0 PD-B
\\.\C:\ifmxdata\wegmans_poc\rootdbs_dat.002
0C72DB20 5 1 0 12500 12497 PO-B
\\.\C:\IFMXDATA\wegmans_poc\wegmans_poc_dat.001
0C71C730 5 1 0 12500 0 MO-B \\.\C:
0C72DCA0 6 5 0 2560 2507 PO-B
c:\IFMXDATA\wegmans_poc\wegmans_poc_dat.002
0C72DE20 7 6 0 2560 2507 PO-B
c:\IFMXDATA\wegmans_poc\rootdbs_dat.002
0C629C90 8 6 0 2560 2557 PO-B
c:\IFMXDATA\wegmans_poc\rootdbs_dat.003
0C629E10 9 5 0 2560 2557 PO-B
c:\IFMXDATA\wegmans_poc\wegmans_poc_dat.003
9 active, 32766 maximum

NOTE: The values in the "size" and "free" columns for DBspace chunks are
displayed in terms of "pgsize" of the DBspace to which they belong.

Expanded chunk capacity mode: always




I had trouble with the query, so I pared it down to retrieve more results
(i.e. removed the where clause)
select dbsname, tabname, st.partnum, hex(st.partnum), nextns,
name as dbspace
from systabnames st, sysptnhdr sp, sysdbspaces sd
where st.partnum = sp.partnum
and sd.dbsnum = (st.partnum / 1048576)

Here's the query output:
no rows



At this point I'm feeling pretty hosed. I don't need *ANYTHING* in my
instance. What's the easiest way to remove EVERYTHING and add a new dbspace
to get going again? Can I use the onspaces -d to remove each dbspace, then
create a dbspace and start over? I'm sure there are many system stored
procedures that I don't want to get rid of... would it be easier to
completely remove Informix, delete IFMXDATA and reinstall? This is a test
instance I set up to try something and it's taking way too long :-(.

Thanks for your assistance!

On Wed, 16 May 2007 14:56:24 GMT, "Bret Schuhmacher"
wrote:

>Here's onstat -d output:
>
>IBM Informix Dynamic Server Version 10.00.TC6TL -- On-Line -- Up 00:57:17
>-- 21568 Kbytes
.... onstat -d output clipped . ..

>
>I had trouble with the query, so I pared it down to retrieve more results
>(i.e. removed the where clause)
>select dbsname, tabname, st.partnum, hex(st.partnum), nextns,
> name as dbspace
>from systabnames st, sysptnhdr sp, sysdbspaces sd
>where st.partnum = sp.partnum
> and sd.dbsnum = (st.partnum / 1048576)
>
>Here's the query output:
>no rows
>

That seems a bit odd.

>
>
>At this point I'm feeling pretty hosed. I don't need *ANYTHING* in my
>instance. What's the easiest way to remove EVERYTHING and add a new dbspace
>to get going again? Can I use the onspaces -d to remove each dbspace, then
>create a dbspace and start over? I'm sure there are many system stored
>procedures that I don't want to get rid of... would it be easier to
>completely remove Informix, delete IFMXDATA and reinstall? This is a test
>instance I set up to try something and it's taking way too long :-(.
>
>Thanks for your assistance!

Here's a quick primer . . .

When a database is created, the dbspace it's created in will contain
various system tables. Apparently, the dbspace that contains these
tables is full. Since the dbspace that holds the database isn't
clear, please answer the following questions.

1. What is the database name?

2. One way to determine the dbspace is to go into onmonitor (not sure
if it's still there in the Windows stack). Choose (S) for status and
(D) for databases ( I think, as I don't have access to an informix
instance at this time.). The database that contains the stored
procedures should show up, and with it, the dbspace that the database
is located in.

3. Depending on the Informix release level, you could also do:
Go into 'dbaccess' using the database name --
dbaccess your_database_name_here

Use the following select
select dbinfo("dbspace", partnum)
from systables
where tabname = "sysprocbody"

4. Another way to find the dbspace could also be
dbaccess sysmaster

select tabname, dbinfo("dbspace", partnum)
from systabnames
where dbsname = "(your db name here)"
and tabname = "sysprocbody"

JWC