HOWTO: SAP IQ Drop Device File from DBSpace

Sometimes when we build an IQ Data Warehouse, our initial space/growth estimate is off significantly enough to warrant reducing the size of the dbspace(s). The process is quite easy put there are a few steps to perform:

Determine how much space is used by the data/indexes. The usage field is the percentage of the totalsize that is being used. For example, the below shows 38% of 4.99TB being used. (4.99 TB * 1024 GB/TB) * 0.38 = 1941.70 GB used:

select convert(varchar(20), DBSpaceName), Usage, TotalSize from sp_iqdbspace() where DBSpaceName = 'IQ_USER_MAIN';
DBSpaceName          Usage TotalSize
------------------------------------
IQ_USER_MAIN         38    4.99T

Determine which device files are part of a dbspace

select convert(varchar(20), DBSpaceName), convert(varchar(20), DBFileName), convert(varchar(70), Path), DBFileSize, Usage from sp_iqfile() where DBSpaceName = 'IQ_USER_MAIN';
DBSpaceName          DBFileName           Path                                                                   DBFileSize Usage
---------------------------------------------------------------------------------------------------------------------------------
IQ_USER_MAIN         IQ_USER_MAIN_FILE_01 /sap/iq/devices/test_usermain001.iq                                    1024G      38
IQ_USER_MAIN         IQ_USER_MAIN_FILE_02 /sap/iq/devices/test_usermain002.iq                                    1024G      38
IQ_USER_MAIN         IQ_USER_MAIN_FILE_03 /sap/iq/devices/test_usermain003.iq                                    1024G      38
IQ_USER_MAIN         IQ_USER_MAIN_FILE_04 /sap/iq/devices/test_usermain004.iq                                    1024G      38
IQ_USER_MAIN         IQ_USER_MAIN_FILE_05 /sap/iq/devices/test_usermain005.iq                                    1024G      34

Verify that the used data will fit in the new allocation

sp_iqdbspace IQ_USER_MAIN

BACKUP DATABASE

backup database FULL to '/backups/test_FULL.dmp';

Alter the file to be dropped to be read only

alter dbspace IQ_USER_MAIN ALTER FILE IQ_USER_MAIN_FILE_05 readonly

Empty the device file (raw device) to be dropped (if dropping more than one file, drop the device file *LEAST* used first)

sp_iqemptyfile 'IQ_USER_MAIN_FILE_05'

Drop the device file (you may need to restart before IQ will let you do this)

ALTER DBSPACE IQ_USER_MAIN DROP FILE IQ_USER_MAIN_FILE_05

Verify the device was dropped

select convert(varchar(20), DBSpaceName), convert(varchar(20), DBFileName), convert(varchar(70), Path), DBFileSize, Usage from sp_iqfile() where DBSpaceName = 'IQ_USER_MAIN';
DBSpaceName          DBFileName           Path                                                                   DBFileSize Usage
---------------------------------------------------------------------------------------------------------------------------------
IQ_USER_MAIN         IQ_USER_MAIN_FILE_01 /sap/iq/devices/test_usermain001.iq                                    1024G      48
IQ_USER_MAIN         IQ_USER_MAIN_FILE_02 /sap/iq/devices/test_usermain002.iq                                    1024G      48
IQ_USER_MAIN         IQ_USER_MAIN_FILE_03 /sap/iq/devices/test_usermain003.iq                                    1024G      45
IQ_USER_MAIN         IQ_USER_MAIN_FILE_04 /sap/iq/devices/test_usermain004.iq                                    1024G      47
Share Button

HOWTO: SAP Sybase IQ Loading Tables with AutoIncrement/Identity with Zero as a value SOLVED

If the source table has an identity or autoincrement field AND the value starts at zero “0” then load table either server -> server or from file will not work.  IQ is hard coded to reject the rows with the zero value for identity/autoincrement using load table.  Creating a staging table with a numeric field instead of identity/autoincrement will not work with the export file because the export file has the field flagged as an identity/autoincrement field.
 
This isn’t documented anywhere.
 
Workaround/resolution:
1. Extract out the source table
2. Create a staging table using the same DDL as the destination table, swapping the identity/autoincrement field with numeric(10,0)
3. Load the table into the staging table
4. Turn on identity_insert for the destination table

Set temporary option identity_insert= 'destination_table';

5. Insert into destination table from staging table

insert into destination_table select * from staging_table;

6. Turn off identity_insert for the destination table

Set temporary option identity_insert = '';

7. Drop staging table
8. Rebuild any views that are invalid
FW Howto: List Invalid Views and fix the views in SAP Sybase IQ

Share Button

HOWTO: So you want to work on production systems? Lessons I’ve learned over the years. Please learn them

1) never implement a change control without ALL the approvals (in writing documented in the change control)
2) always have a minimum of two database thresholds (if your DBMS supports threshold actions)
3) always communicate with the team what is being changed and why (document the sh*t out of it – CYA)
4) always verify the change was implemented correctly (verification should be another person if possible) and exhibits the correct behavior afterwards. Be ready to back out the change
5) production changes should only occur no less than one week after non-prod. Let it bake in and monitor it carefully
6) the change control should have all the steps:

a) explicitly state which systems are effected
b) provide snapshot of the state of the database (database or table backup, options, etc)
c) include any dml/ddl code that will take place and expected results (when performed store the output within the change control if possible)
d) implementor verification (did it work? any issues?)
e) *ANOTHER PERSON* performs another verification
f) include back out steps. How long will the back out take?

7) if the change control goes bad, there should be enough time to back out the change within the maintenance window. *always* get the manager on duty to sign off that the change had to be backed out.

We all have to learn the lessons the hard way it seems.

Share Button

SAP IQ: Error: server ‘iq_mpx_1’ was started on an incorrect host ‘myhost1’: this server was created with this connection string ‘host=myhost11:5535’ SOLVED

Recently I built a SAP_IQSAP IQ Multiplex cluster and ran into a self inflicted issue. After I configured the secondary nodes I updated the coordinator node (primary node) with the private (interconnect) and public (what you connect to with an application) connection information. Problem was, I made a small typo and didn’t catch it until after I tried starting the coordinator node.

I configured the coordinator node as such:

alter multiplex server ip_mpx_1 database '/sybase_iq/iq_mpx.db' PRIVATE HOST 'node1-clu' PORT 5535 HOST 'myhost11' port 5535;

Upon attempting to start the coordinator node it failed to start with the following message:

MPX: startup failure message: server 'iq_mpx_1' was started on an incorrect host 'myhost1': this server was created with this connection string 'host=myhost11:5535
-- (stcxtlib/st_database.cxx 9455)
Database server shutdown due to startup error

As soon as I saw the message I swore but the fix is quite simple. First, shutdown any secondary nodes. Update your IQ configuration file (or start command line options) so it starts in single node mode and overrides the multiplex configuration:

# single node mode
-iqmpx_sn 1

#For use starting multiplex databases only. Starts the server with override to acknowledge that the write server is starting (1) on a different host, (2) with a different server name, or (3) using a different path to its catalog (.db) file. Do not start two write servers against the same database.
-iqmpx_ov 1

Start the IQ coordinator and reissue the alter multiplex command:

alter multiplex server ip_mpx_1 database '/sybase_iq/iq_mpx.db' PRIVATE HOST 'node1-clu' PORT 5535 HOST 'myhost1' port 5535;

Update your IQ configuration file to either remove or comment out the lines we added earlier.

Start up your coordinator. It should now start fine. Please note you will need to resync your secondary nodes before starting them.

Share Button