[IQUG] How can I drop one of IQ_SYSTEM_MAIN and IQ_SYSTEM_TEMP dbspaces?

Steve Shen sshen at sscinc.com
Fri Jul 20 17:40:27 MST 2018


Hi Harpreet,

I just saw your reply.

I am very happy knowing that those messages were well documented and they were normal in Linux using file systems.

I am more relaxed now.  Thank you very much!

Enjoy your weekend!

Regards,
Steve Shen
From: Chaudhary, Harpreet [mailto:harpreet.chaudhary at sap.com]
Sent: Friday, July 20, 2018 3:45 PM
To: Mark Mumy; Steve Shen
Cc: iqug at iqug.org
Subject: RE: [IQUG] How can I drop one of IQ_SYSTEM_MAIN and IQ_SYSTEM_TEMP dbspaces?

Hi Steve,

I believe you are using file system dbspaces on Linux.
The messages are normal , see https://help.sap.com/viewer/a898a5b484f21015a377cd1ccb6ee9b5/16.0.11/en-US/c704888fb2bf47e99908498ed1d9f042.html for information on posix_fallocate messages. You may find KBA https://launchpad.support.sap.com/#/notes/2522495 helpful as well

Regards,
Harpreet

From: iqug-bounces at iqug.org<mailto:iqug-bounces at iqug.org> <iqug-bounces at iqug.org<mailto:iqug-bounces at iqug.org>> On Behalf Of Mark Mumy
Sent: Friday, July 20, 2018 3:35 PM
To: Steve Shen <sshen at sscinc.com<mailto:sshen at sscinc.com>>
Cc: iqug at iqug.org<mailto:iqug at iqug.org>
Subject: Re: [IQUG] How can I drop one of IQ_SYSTEM_MAIN and IQ_SYSTEM_TEMP dbspaces?

When you upgrade, reset all core and dml options to default. They do change behavior from version to version.  I had one customer that took an average of a 100% slow down on all queries because of this.

Reset them this way:

    Set option public.xxx=;



Mark

========================

Sent from my mobile device

On Jul 20, 2018, at 14:04, Steve Shen <sshen at sscinc.com<mailto:sshen at sscinc.com>> wrote:
Hi Mark,

All the following options were ON even though I did not touch any of them at all:

PUBLIC.DML_Options21
PUBLIC.DML_Options30
PUBLIC.collect_statistics_on_dml_updates

PUBLIC.CORE_Options4
PUBLIC.CORE_Options59
PUBLIC.CORE_Options94
PUBLIC.CORE_Options95
PUBLIC.CORE_Options96
PUBLIC.CORE_Options154

PUBLIC.Mpx_GTR_Enabled

Should I disable any of these PUBLIC options?  Please advise.

Thank you.
Steve Shen
From: Mumy, Mark [mailto:mark.mumy at sap.com]
Sent: Friday, July 20, 2018 8:23 AM
To: Steve Shen; 'iqug at iqug.org<mailto:iqug at iqug.org>'; 'Mark Mumy'
Subject: Re: How can I drop one of IQ_SYSTEM_MAIN and IQ_SYSTEM_TEMP dbspaces?

It looks like you have some option turned on that is outputting  that data.  It is not normal.

Did you reset all your set options, especially the DML_, CORE, MPX, etc?  Adjust the cfg file for IQ 16 over 15?

Mark

Mark Mumy
Strategic Technology Incubation Group
SAP Global Center of Excellence |  SAP
M +1 347-820-2136 | E mark.mumy at sap.com<mailto:mark.mumy at sap.com>
My Blogs: https://blogs.sap.com/author/markmumy/

https://sap.na.pgiconnect.com/I825063
Conference tel: 18663127353,,8035340905#

From: Steve Shen <sshen at sscinc.com<mailto:sshen at sscinc.com>>
Date: Thursday, July 19, 2018 at 12:39 PM
To: "iqug at iqug.org<mailto:iqug at iqug.org>" <iqug at iqug.org<mailto:iqug at iqug.org>>, "markdmumy at gmail.com<mailto:markdmumy at gmail.com>" <markdmumy at gmail.com<mailto:markdmumy at gmail.com>>, Mark Mumy <mark.mumy at sap.com<mailto:mark.mumy at sap.com>>
Subject: RE: How can I drop one of IQ_SYSTEM_MAIN and IQ_SYSTEM_TEMP dbspaces?

Today Topic:
1.       Many lines of new messages were appended to the IQ version 16.0 server standard error file at Red Hat version 7.4 after the full database were loaded.

Hi Mark and all,

I agreed with Mark that reading SAP/Sybase technical manuals on those specific sections related to the topic were very much like walking through the mud during raining days.

So I decided to NOT modify the default behavior while creating the IQ version 16 using file systems at Linux.  I kept both suffixes on “.iq” and “.iqtmp”.

I was successful in migrating IQ version 15.4 using raw partitions at Solaris X64 to version 16.0 at Solaris X64 last Saturday.  Both source and target IQs used raw partitions.

I managed to take the full backups from IQ version 16.x at Solaris X64 using raw partitions and loading the full backups to IQ version 16.x at Linux using file systems today.

Both sp_iqcheckdb 'verify database' and sp_iqcheckdb 'allocation database' on the IQ database migrated to Red Hat 7.4 showed “No Errors Detected”   twice.

I used the most latest version at 16.0. The IQ at Solaris X64 and Linux are listed below:

SAP IQ/16.0.110.2805/11355/P/sp11.20/Sun_x64/OS 5.10/64bit/2018-06-15 04:03:52

SAP IQ/16.0.110.2805/11355/P/sp11.20/Enterprise Linux64 - x86_64 - 2.6.18-194.el5/64bit/2018-06-15 02:57:16

Many lines of additional information were appended to the server standard error file after the full backups was loaded to the Linux.
I did have one concerns because I observed that there were many lines found in /siqsoftware/siq160SP011PL20/IQ-16_0/logfiles/utility_db.0012.stderr:

“
…
Total number of IQ blocks being restored: 35917859
Total number of IQ blocks being restored: 35967884
Total number of IQ blocks being restored: 36017904
Total number of IQ blocks being restored: 36067929
Total number of IQ blocks restored: 36108287  at 2018-07-19 11:38:11
    IQ blocksize = 8192
    backup blocksize = 8232
    blocking factor = 25
    buffersize = 205816
ValidateRestore
PrepareForRestore
DoRestore
139699057977088 Info: posix_fallocate fd=21 sample time 0.000000 sec.
139716895168256 Info: posix_fallocate fd=18 sample time 0.000000 sec.
139699059562240 Info: posix_fallocate fd=23 sample time 0.000000 sec.
139699058505472 Info: posix_fallocate fd=19 sample time 0.000000 sec.
139699060619008 Info: posix_fallocate fd=25 sample time 0.000000 sec.
139699059033856 Info: posix_fallocate fd=24 sample time 0.000000 sec.
139699061147392 Info: posix_fallocate fd=22 sample time 0.000000 sec.
139699060090624 Info: posix_fallocate fd=26 sample time 0.000000 sec.
139699057448704 Info: posix_fallocate fd=20 sample time 0.000000 sec.
139699061675776 Info: posix_fallocate fd=27 sample time 0.000000 sec.
139699059562240 Info: posix_fallocate fd=23 second sample time 0.000000 sec.
139699059562240 Info: posix_fallocate fd=23 sample time 0.000000 sec.
139716895168256 Info: posix_fallocate fd=18 second sample time 0.000000 sec.
139716895168256 Info: posix_fallocate fd=18 sample time 0.000000 sec.
139699059562240 Info: using posix_fallocate fd=23.  Sampling threshold was 0.010000 sec.
139716895168256 Info: using posix_fallocate fd=18.  Sampling threshold was 0.010000 sec.
139699058505472 Info: posix_fallocate fd=19 second sample time 0.000000 sec.
139699058505472 Info: posix_fallocate fd=19 sample time 0.000000 sec.
139699060619008 Info: posix_fallocate fd=25 second sample time 0.000000 sec.
139699060619008 Info: posix_fallocate fd=25 sample time 0.000000 sec.
139699058505472 Info: using posix_fallocate fd=19.  Sampling threshold was 0.010000 sec.
139699060619008 Info: using posix_fallocate fd=25.  Sampling threshold was 0.010000 sec.
139699057977088 Info: posix_fallocate fd=21 second sample time 0.000000 sec.
139699057977088 Info: posix_fallocate fd=21 sample time 0.000000 sec.
139699057977088 Info: using posix_fallocate fd=21.  Sampling threshold was 0.010000 sec.
139699059033856 Info: posix_fallocate fd=24 second sample time 0.000000 sec.
139699059033856 Info: posix_fallocate fd=24 sample time 0.000000 sec.
139699059033856 Info: using posix_fallocate fd=24.  Sampling threshold was 0.010000 sec.
139699061147392 Info: posix_fallocate fd=22 second sample time 0.000000 sec.
139699061147392 Info: posix_fallocate fd=22 sample time 0.000000 sec.
139699061147392 Info: using posix_fallocate fd=22.  Sampling threshold was 0.010000 sec.
139699060090624 Info: posix_fallocate fd=26 second sample time 0.000000 sec.
139699060090624 Info: posix_fallocate fd=26 sample time 0.000000 sec.
139699060090624 Info: using posix_fallocate fd=26.  Sampling threshold was 0.010000 sec.
139699061675776 Info: posix_fallocate fd=27 second sample time 0.000000 sec.
139699061675776 Info: posix_fallocate fd=27 sample time 0.000000 sec.
139699061675776 Info: using posix_fallocate fd=27.  Sampling threshold was 0.010000 sec.
139699057448704 Info: posix_fallocate fd=20 second sample time 0.000000 sec.
139699057448704 Info: posix_fallocate fd=20 sample time 0.000000 sec.
139699057448704 Info: using posix_fallocate fd=20.  Sampling threshold was 0.010000 sec.
139716895168256 Info: posix_fallocate fd=28 sample time 0.000000 sec.
139716895168256 Info: posix_fallocate fd=28 second sample time 0.000000 sec.
139716895168256 Info: posix_fallocate fd=28 sample time 0.000000 sec.
139716895168256 Info: using posix_fallocate fd=28.  Sampling threshold was 0.010000 sec.
139716895168256 Info: posix_fallocate fd=29 sample time 0.000000 sec.
139716895168256 Info: posix_fallocate fd=29 second sample time 0.000000 sec.
139716895168256 Info: posix_fallocate fd=29 sample time 0.000000 sec.
139716895168256 Info: using posix_fallocate fd=29.  Sampling threshold was 0.010000 sec.
139716895168256 Info: posix_fallocate fd=30 sample time 0.000000 sec.
139716895168256 Info: posix_fallocate fd=30 second sample time 0.000000 sec.
139716895168256 Info: posix_fallocate fd=30 sample time 0.000000 sec.
139716895168256 Info: using posix_fallocate fd=30.  Sampling threshold was 0.010000 sec.
Using licenses from: /siqsoftware/siq160SP011PL20/SYSAM-2_0/licenses/hrs1sybprd32_20180717_180057.lic:/siqsoftware/sybase/*.lic
Checked out license for 16 IQ_CORE (9999.12310/permanent/02F5 8096 3F65 2030).
*****-----*****-----*****-----*****-----*****
hos_Profiler::Dump()
*****-----*****-----*****-----*****-----*****
Checked in license for 16 IQ_CORE (9999.12310/permanent/02F5 8096 3F65 2030).
Checked in license for 16 IQ_CORE (9999.12310/permanent/02F5 8096 3F65 2030).
139699059562240 Info: posix_fallocate fd=23 second sample time 0.000000 sec.
139699059562240 Info: posix_fallocate fd=23 sample time 0.000000 sec.
139716895168256 Info: posix_fallocate fd=18 second sample time 0.000000 sec.
139716895168256 Info: posix_fallocate fd=18 sample time 0.000000 sec.
139699059562240 Info: using posix_fallocate fd=23.  Sampling threshold was 0.010000 sec.
139716895168256 Info: using posix_fallocate fd=18.  Sampling threshold was 0.010000 sec.
139699058505472 Info: posix_fallocate fd=19 second sample time 0.000000 sec.
139699058505472 Info: posix_fallocate fd=19 sample time 0.000000 sec.
139699060619008 Info: posix_fallocate fd=25 second sample time 0.000000 sec.
139699060619008 Info: posix_fallocate fd=25 sample time 0.000000 sec.
139699058505472 Info: using posix_fallocate fd=19.  Sampling threshold was 0.010000 sec.
139699060619008 Info: using posix_fallocate fd=25.  Sampling threshold was 0.010000 sec.
139699057977088 Info: posix_fallocate fd=21 second sample time 0.000000 sec.
139699057977088 Info: posix_fallocate fd=21 sample time 0.000000 sec.
139699057977088 Info: using posix_fallocate fd=21.  Sampling threshold was 0.010000 sec.
139699059033856 Info: posix_fallocate fd=24 second sample time 0.000000 sec.
139699059033856 Info: posix_fallocate fd=24 sample time 0.000000 sec.
139699059033856 Info: using posix_fallocate fd=24.  Sampling threshold was 0.010000 sec.
139699061147392 Info: posix_fallocate fd=22 second sample time 0.000000 sec.
139699061147392 Info: posix_fallocate fd=22 sample time 0.000000 sec.
139699061147392 Info: using posix_fallocate fd=22.  Sampling threshold was 0.010000 sec.
139699060090624 Info: posix_fallocate fd=26 second sample time 0.000000 sec.
139699060090624 Info: posix_fallocate fd=26 sample time 0.000000 sec.
139699060090624 Info: using posix_fallocate fd=26.  Sampling threshold was 0.010000 sec.
139699061675776 Info: posix_fallocate fd=27 second sample time 0.000000 sec.
139699061675776 Info: posix_fallocate fd=27 sample time 0.000000 sec.
139699061675776 Info: using posix_fallocate fd=27.  Sampling threshold was 0.010000 sec.
139699057448704 Info: posix_fallocate fd=20 second sample time 0.000000 sec.
139699057448704 Info: posix_fallocate fd=20 sample time 0.000000 sec.
139699057448704 Info: using posix_fallocate fd=20.  Sampling threshold was 0.010000 sec.
139716895168256 Info: posix_fallocate fd=28 sample time 0.000000 sec.
139716895168256 Info: posix_fallocate fd=28 second sample time 0.000000 sec.
139716895168256 Info: posix_fallocate fd=28 sample time 0.000000 sec.
139716895168256 Info: using posix_fallocate fd=28.  Sampling threshold was 0.010000 sec.
139716895168256 Info: posix_fallocate fd=29 sample time 0.000000 sec.
139716895168256 Info: posix_fallocate fd=29 second sample time 0.000000 sec.
139716895168256 Info: posix_fallocate fd=29 sample time 0.000000 sec.
139716895168256 Info: using posix_fallocate fd=29.  Sampling threshold was 0.010000 sec.
139716895168256 Info: posix_fallocate fd=30 sample time 0.000000 sec.
139716895168256 Info: posix_fallocate fd=30 second sample time 0.000000 sec.
139716895168256 Info: posix_fallocate fd=30 sample time 0.000000 sec.
139716895168256 Info: using posix_fallocate fd=30.  Sampling threshold was 0.010000 sec.
Using licenses from: /siqsoftware/siq160SP011PL20/SYSAM-2_0/licenses/hrs1sybprd32_20180717_180057.lic:/siqsoftware/sybase/*.lic
Checked out license for 16 IQ_CORE (9999.12310/permanent/02F5 8096 3F65 2030).
*****-----*****-----*****-----*****-----*****
hos_Profiler::Dump()
*****-----*****-----*****-----*****-----*****
Checked in license for 16 IQ_CORE (9999.12310/permanent/02F5 8096 3F65 2030).
Checked in license for 16 IQ_CORE (9999.12310/permanent/02F5 8096 3F65 2030).Thank you.
“

Were they normal to Linux only?  Or were they normal to IQ version 16.0?  If those messages were normal, what purpose did they serve to the clients?

Thank you.

Kind regards,
Steve Shen

From: Steve Shen
Sent: Tuesday, July 17, 2018 9:03 PM
To: 'Mark Mumy'
Cc: iqug at iqug.org<mailto:iqug at iqug.org>; Mumy, Mark
Subject: RE: How can I drop one of IQ_SYSTEM_MAIN and IQ_SYSTEM_TEMP dbspaces?

Mark,

I did touch two files the way I wanted before creating the database; but IQ still automatically created two new files and plugged in “.iq’ and ‘.iqtmp’.

Tomorrow I will try what you suggested on those two system database spaces.

Thank you.

Regards,
Steve Shen
From: Mark Mumy [mailto:markdmumy at gmail.com]
Sent: Tuesday, July 17, 2018 8:44 PM
To: Steve Shen
Cc: iqug at iqug.org<mailto:iqug at iqug.org>; Mumy, Mark
Subject: Re: How can I drop one of IQ_SYSTEM_MAIN and IQ_SYSTEM_TEMP dbspaces?

If it’s a multiplex you need to bring it up in simplex mode.

Mark the file as read only with alter dbspace alter file.  Then run sp_iqrelocate on the file.  Once done, use alter dbspace drop file to drop it.

For filesystem devices we have always defaulted to adding the .iq. I think you can touch the file before doing anything in IQ.  Then we see that it exists and use it.  In theory.....

Mark

========================

Sent from my mobile device

On Jul 17, 2018, at 19:10, Steve Shen <sshen at sscinc.com<mailto:sshen at sscinc.com>> wrote:
Topic:  How can I drop one file from IQ_SYSTEM_MAIN and IQ_SYSTEM_TEMP dbspaces at IQ version 16.x?

At the IQ database creation time using file systems at Linux, IQ automatically plugged in suffix “.iq” on IQ_SYSTEM_MAIN and “.iqtmp” to IQ_SYSTEM_TEMP.
Refer to the following:

sybase at hrs1sybprd32: /iq_main_temp_store/siqcgsuat_cognosdw ==> ls -tl $PWD/cognosdw_system_main*
-rw-r----- 1 sybase sybase 53687091200 Jul 17 19:42 /iq_main_temp_store/siqcgsuat_cognosdw/cognosdw_system_main.iq

sybase at hrs1sybprd32: /iq_main_temp_store/siqcgsuat_cognosdw ==> ls -lt $PWD/cognosdw_temp1*
-rw-r----- 1 sybase sybase 53687091200 Jul 17 19:42 /iq_main_temp_store/siqcgsuat_cognosdw/cognosdw_temp1.iqtmp

This default behavior was not desirable from my perspective.  I had no way to override this behavior at the database creation time.
IQ engineers should not have modified the physical file names even if I put single quotes to the physical paths.
If you knew that there was a way to override this undesirable behavior, please let me know.

So I added one more file, cognosdw_system_main, to IQ_SYSTEM_MAIN.

I also added one more file, cognosdw_temp1, to IQ_SYSTEM_TEMP.

Refer to the following:

sybase at hrs1sybprd32: /iq_main_temp_store/siqcgsuat_cognosdw ==> ls -tl $PWD/cognosdw_system_main*
-rw-r----- 1 sybase sybase 53687091200 Jul 17 19:42 /iq_main_temp_store/siqcgsuat_cognosdw/cognosdw_system_main.iq
-rw-r----- 1 sybase sybase 53687091200 Jul 17 19:42 /iq_main_temp_store/siqcgsuat_cognosdw/cognosdw_system_main

sybase at hrs1sybprd32: /iq_main_temp_store/siqcgsuat_cognosdw ==> ls -lt $PWD/cognosdw_temp1*
-rw-r----- 1 sybase sybase 53687091200 Jul 17 19:42 /iq_main_temp_store/siqcgsuat_cognosdw/cognosdw_temp1.iqtmp
-rw-r----- 1 sybase sybase 53687091200 Jul 17 19:42 /iq_main_temp_store/siqcgsuat_cognosdw/cognosdw_temp1

Is there an easy way to drop “/iq_main_temp_store/siqcgsuat_cognosdw/cognosdw_system_main.iq” from IQ_SYSTEM_MAIN?

Is there an easy way to drop “/iq_main_temp_store/siqcgsuat_cognosdw/cognosdw_temp1.iqtmp” from IQ_SYSTEM_TEMP?

Notes: I have not begun using RLV yet.

Please advise.  Thank you.

Regards,
Steve Shen


This email with all information contained herein or attached hereto may contain confidential and/or privileged information intended for the addressee(s) only. If you have received this email in error, please contact the sender and immediately delete this email in its entirety and any attachments thereto.
This email with all information contained herein or attached hereto may contain confidential and/or privileged information intended for the addressee(s) only. If you have received this email in error, please contact the sender and immediately delete this email in its entirety and any attachments thereto.
This email with all information contained herein or attached hereto may contain confidential and/or privileged information intended for the addressee(s) only. If you have received this email in error, please contact the sender and immediately delete this email in its entirety and any attachments thereto.
This email with all information contained herein or attached hereto may contain confidential and/or privileged information intended for the addressee(s) only. If you have received this email in error, please contact the sender and immediately delete this email in its entirety and any attachments thereto.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://iqug.org/pipermail/iqug/attachments/20180721/63986016/attachment-0001.html>


More information about the IQUG mailing list