Not able to see outpot of procedure

[Former Member]

When i am executing sp_iqspaceused it is executing .With 1 Row(s) affected in result set.But I am not able to see its output.What the problem behind it.How can i see result.

[tayeb_hadjou · Accepted solution]

Hi Arpit,

As mentioned in IQ manual, sp_iqspaceused requires seven output parameters. Create a user-defined stored procedure myspace that declares the seven output parameters, then calls sp_iqspaceused:

http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc01774.1604/doc/html/san1278453328180...

mainMB,mainusedMB,mainPerCent,tempMB,tempusedMB,tempPerCent,shTempTotalKB,shTempTotalKBUsed,globalshtempPerCent,shTempLocalMB,shTempLocalKBUsed,localshtempPerCent 9224,232,2,4500,0,0,0,0,0,0,0,0

Regards, Tayeb

[Former Member · Accepted solution]

Thanks Tayeb. Yes i got it.i have one doubt .Does first column MainMB after executing myspace procedure represent db Size ?

[tayeb_hadjou · Accepted solution]

The fist column is size allocated to main store. The second one is the effective data used in Main Store.

[Former Member · Accepted solution]

And if we talk about what is size of IQ Database .Like we can see with sp_helpdb in Sybase ASE if you are familiar with it .

[tayeb_hadjou · Accepted solution]

No exact equivalent to ASE sp_helpdb as IQ has different storage structure (main, temp, tran log).

To find info about size, free space etc : sp_iqdbsize, sp_iqdbspace, sp_iqstatus, myspace (based on sp_iqspaceused). You can choose one of them or combine depending on exact information you need.

For example, sp_iqstatus provides info below and other  :

Name,Value

' Main IQ Blocks Used:','29698 of 1155072, 2%=232Mb, Max Block#: 31046'

' Shared Temporary IQ Blocks Used:','0 of 0, 0%=0Mb, Max Block#: 0'

' Local Temporary IQ Blocks Used:','114 of 550400, 0%=0Mb, Max Block#: 1045504'

When value is expressed in blocks, you can convert to KB :

(database block size) * (number of blocks).

Where Database Block size  is : select block_size/1024 as block_size_KB from SYSIQINFO

[Former Member · Accepted solution]

hey Tayeb thanks for replying .As per your above post I have little bit confusion again.

According to my research .Size of a IQ Database = MainMB + TempMB

Where :

Main IQ Blocks Used + Main Reserved Blocks Available = MainMB

Local Temporary IQ Blocks Used + Local Temporary Reserved Blocks Available = tempMB

Please correct me if am going in wrong way.I am getting Confused

1).o/p of sp_iqdbsize .

seeing o/p Here how do we will know size of db , or what info. we will get by physical block column. ?

2).o/p of sp_iqdbspace.

3).o/p of sp_iqstatus.

4).o/p of mysapce.

From seeing o/p of above 4 results ,I am not able to conclude finally what we have to see to know IQ database Size.

What is Size of DB .May be its 225 MB. 200 main + 25 Temp.By seeing o/p of myspace & sp_iqdbspace and Making calculation from o/p of .But unable to correlate  o/p of sp_iqdbsize

[Former Member · Accepted solution]

any one to help please.I need to know only what info will get from o/p of  sp_iqdbsize.

1).o/p of sp_iqdbsize .

seeing o/p Here how do we will know size of db , or what info. we will get by physical block column. ?

[tayeb_hadjou · Accepted solution]

Hi Arprit,

The sp_iqdbsize displays only the main store actually used.

in your case It is 5944 blocks = 46.4 MB (aprox).

It is consistent with

myspace o/p  : mainusedMLB=46

and with sp_iqstatus o/p : Line 24 Main IQ blocks used=46MB

and with sp_iqdbspace :20+26=46MB.

Same for temp space: its usage : tempusedMB.

If you mean total space (free, used and reserved) to know for example the required space to restore/copy database to another location :

Total database size : sum(TotalSize+Reserve) from sp_iqdbspace()=  525 MB.

Note that "Reserved" in IQ, means database files created with "Reserve" clause for a future extension purpose.

Regards,

Tayeb.