IQ Main Store usage for SAP BW NLS Data

[Former Member]

would like to seek HELP and expertise on how to manage the IQ Main Store of SYBASE IQ 16 SP10 to store NLS data that was archived from HANA DB.

We are now in the DEV phase for NLS implem and are starting the archiving.

Solution is working fine for us now since DEV is containing small amount of data. BUT it is a problem in the future if the company wants to go for a multiplex architecture.

What is the Best Practice for storing data to Sybase iq?

As per IQ sizing docu, IQ_SYSTEM_MAIN dbspace manages important db sturctures including the free list, which lists blocks in use, TLV, node-to-node system coomunication for object changes, and other structures for proper operation of Sybase iq. it more space needed in iq_system_main to store user data, more space will be reserved. Also, when adding space to iq_system_main the entire multiplex must be shut down and the nodes sync.

in our case now, the nls tables are in iq_system_main. Should NLS table be in another DBSPace in Main Store? What steps are to be done to have this maintained? Since we also need to ensure that when we have a separate dbspace for nls, we should ensure we limit the access to the iq_system_main.

HELP US!!!

Regards,

Joan

[RolandKramer]

Hi,

As already stated by Chris, the correct SAP IQ DB Implementation is mandantory.  See the SAP First Guidance Document for that -

Best Regards Roland

[c_baker]

I would suggest reviewing the following First Guidance document:

http://scn.sap.com/docs/DOC-39627

It contains a lot of detail about using IQ as the BW NLS, but to answer your question - Yes, you should have a separate DBSpace for the NLS data.

Chris

[c_baker]

Also, there is a separate community for this that can help answer some of your questions:

http://scn.sap.com/community/data-warehousing/bw

This is where the above whitepaper is housed.

Chris

[Former Member]

Thanks Chris, we tried the steps from creating the dbspace to extending the dbfiles and enhancing the db options.

We restarted the iq after that to reflect the changes.

But then when we create a data archiving process and activate it in sap bw, it fails due to

[Former Member]

sqlcode=-121: Permission denied: you do not have permission to create objects in dbspace "IQ_SYSTEM_MAIN"

it's still pointing to the old dbspace

[Former Member]

do you have an idea how this could be fixed?

[c_baker]

Run the following command as dba:

set option public.default_dbspace = '<your NLS dbspace>';

and re-connect the NLS user.

You can also set it at the user instead of public level, but this ensures that any connect user issuing a DDL command will operate in the other db_space.

Note: objects already in IQ_SYSTEM_MAIN will stay there - including existing tables that NLS might add partitions to.  I think you have to recreate (under a new name), copy the data, and use sp_iqrename on the new table to rename it to the old name, after you drop the old table - to move the table to the new dbspace.

Chris

[Former Member]

Hi Chris,

Thanks for this.. Bad news though, we have decided to recreate the db instead.. the iq db.. due to the data in the new dbspace has incorrect values... then when the iq db is up, we tried to archive and it seems that the old dap are still pointing to the old settings..

any recommendation how to fix this? we tried to delete the dap, but it is having error now..

something like error in accessing the relocated data (archive or near-line storage)..

We do not know what to do next.. SAP is not yet responding to us..

I tried to look for a fix in SAP but this seems to be a unique scenario.

Regards,

Joan