Skip to content

Instantly share code, notes, and snippets.

@rayhassan

rayhassan/sybase-IQ-16.1-notes

Created July 27, 2018 12:31
Show Gist options
  • Save rayhassan/d6f50629cc3a36b7e10d96aa56265211 to your computer and use it in GitHub Desktop.
Save rayhassan/d6f50629cc3a36b7e10d96aa56265211 to your computer and use it in GitHub Desktop.
Download ZIP
Sybase IQ 16.1 - Simplex creation and Multiplex conversion
Raw
sybase-IQ-16.1-notes
The devices were partitions that served as a SAP Sybase IQ consistency group, and mapped to an EMC VNX 5700 with 9 SSDs configured to provide 20 x 35 GB devices. The devices were mapped to dbspace files as follows:
2 devices for shared IQ_SYSTEM_MAIN
12 devices for shared IQ_USER_MAIN
2 devices for shared IQ_SHARED_TEMP
2 devices for node 1 IQ_SYSTEM_TEMP
2 devices for node 2 IQ_SYSTEM_TEMP
1 device (450 GB) for ext4 filesystem used to host source load data (node 1)
brw-rw----. 1 root disk 8, 16 Jul 17 08:17 /dev/sdb
brw-rw----. 1 root disk 8, 32 Jul 17 08:17 /dev/sdc
brw-rw----. 1 root disk 8, 48 Jul 17 08:17 /dev/sdd
brw-rw----. 1 root disk 8, 64 Jul 17 08:17 /dev/sde
brw-rw----. 1 root disk 8, 80 Jul 17 08:17 /dev/sdf
brw-rw----. 1 root disk 8, 96 Jul 17 08:17 /dev/sdg
brw-rw----. 1 root disk 8, 112 Jul 17 08:17 /dev/sdh
brw-rw----. 1 root disk 8, 128 Jul 17 08:17 /dev/sdi
used on ntnx....
ACTION=="add", ENV{MAJOR}=="8", ENV{MINOR}=="16", RUN+="/bin/raw /dev/raw/raw1 %M %m"
ACTION=="add", ENV{MAJOR}=="8", ENV{MINOR}=="32", RUN+="/bin/raw /dev/raw/raw2 %M %m"
ACTION=="add", ENV{MAJOR}=="8", ENV{MINOR}=="48", RUN+="/bin/raw /dev/raw/raw3 %M %m"
ACTION=="add", ENV{MAJOR}=="8", ENV{MINOR}=="64", RUN+="/bin/raw /dev/raw/raw4 %M %m"
ACTION=="add", ENV{MAJOR}=="8", ENV{MINOR}=="80", RUN+="/bin/raw /dev/raw/raw5 %M %m"
ACTION=="add", ENV{MAJOR}=="8", ENV{MINOR}=="96", RUN+="/bin/raw /dev/raw/raw6 %M %m"
ACTION=="add", ENV{MAJOR}=="8", ENV{MINOR}=="112", RUN+="/bin/raw /dev/raw/raw7 %M %m"
ACTION=="add", ENV{MAJOR}=="8", ENV{MINOR}=="128", RUN+="/bin/raw /dev/raw/raw8 %M %m"
#/etc/udev/rules.d/60-raw.rules
# sybase-virutalnode1 / sybase-virtualnode2 raw device bindings
# IQ_SYSTEM_MAIN shared rw dbspace file devices
ACTION=="add", ENV{MAJOR}=="8", ENV{MINOR}=="16", RUN+="/bin/raw /dev/raw/raw1 %M %m"
ACTION=="add", ENV{MAJOR}=="8", ENV{MINOR}=="32", RUN+="/bin/raw /dev/raw/raw2 %M %m"
# IQ_USER_MAIN shared rw dbspace file devices
ACTION=="add", ENV{MAJOR}=="8", ENV{MINOR}=="48", RUN+="/bin/raw /dev/raw/raw3 %M %m"
ACTION=="add", ENV{MAJOR}=="8", ENV{MINOR}=="64", RUN+="/bin/raw /dev/raw/raw4 %M %m"
ACTION=="add", ENV{MAJOR}=="8", ENV{MINOR}=="80", RUN+="/bin/raw /dev/raw/raw5 %M %m"
ACTION=="add", ENV{MAJOR}=="8", ENV{MINOR}=="96", RUN+="/bin/raw /dev/raw/raw6 %M %m"
ACTION=="add", ENV{MAJOR}=="8", ENV{MINOR}=="112", RUN+="/bin/raw /dev/raw/raw7 %M %m"
ACTION=="add", ENV{MAJOR}=="8", ENV{MINOR}=="128", RUN+="/bin/raw /dev/raw/raw8 %M %m"
ACTION=="add", ENV{MAJOR}=="8", ENV{MINOR}=="144", RUN+="/bin/raw /dev/raw/raw9 %M %m"
ACTION=="add", ENV{MAJOR}=="8", ENV{MINOR}=="160", RUN+="/bin/raw /dev/raw/raw10 %M %m"
ACTION=="add", ENV{MAJOR}=="8", ENV{MINOR}=="192", RUN+="/bin/raw /dev/raw/raw11 %M %m"
ACTION=="add", ENV{MAJOR}=="8", ENV{MINOR}=="208", RUN+="/bin/raw /dev/raw/raw12 %M %m"
ACTION=="add", ENV{MAJOR}=="8", ENV{MINOR}=="224", RUN+="/bin/raw /dev/raw/raw13 %M %m"
ACTION=="add", ENV{MAJOR}=="8", ENV{MINOR}=="240", RUN+="/bin/raw /dev/raw/raw14 %M %m"
# LOCAL IQ_SYSTEM_TEMP NODE 1 - will not be shared but is mounted from the same group - only rw from node 1
ACTION=="add", ENV{MAJOR}=="65", ENV{MINOR}=="0", RUN+="/bin/raw /dev/raw/raw15 %M %m"
ACTION=="add", ENV{MAJOR}=="65", ENV{MINOR}=="16", RUN+="/bin/raw /dev/raw/raw16 %M %m"
# LOCAL IQ_SYSTEM_TEMP NODE 2 - will not be shared but is mounted from the same group - only rw from node 2
ACTION=="add", ENV{MAJOR}=="65", ENV{MINOR}=="32", RUN+="/bin/raw /dev/raw/raw17 %M %m"
ACTION=="add", ENV{MAJOR}=="65", ENV{MINOR}=="48", RUN+="/bin/raw /dev/raw/raw18 %M %m"
# IQ_SHARED_TEMP shared rw dbspace file devices
ACTION=="add", ENV{MAJOR}=="65", ENV{MINOR}=="64", RUN+="/bin/raw /dev/raw/raw19 %M %m"
ACTION=="add", ENV{MAJOR}=="65", ENV{MINOR}=="80", RUN+="/bin/raw /dev/raw/raw20 %M %m"
#/etc/udev/rules.d/99-raw.rules
#change ownership of raw devices to ‘sybase’
#change access of /dev/raw/rawctl to allow ‘sybase’ to read/write
KERNEL=="raw1", OWNER="sybase", GROUP="sybase", MODE="640"
KERNEL=="raw2", OWNER="sybase", GROUP="sybase", MODE="640"
KERNEL=="raw3", OWNER="sybase", GROUP="sybase", MODE="640"
KERNEL=="raw4", OWNER="sybase", GROUP="sybase", MODE="640"
KERNEL=="raw5", OWNER="sybase", GROUP="sybase", MODE="640"
KERNEL=="raw6", OWNER="sybase", GROUP="sybase", MODE="640"
KERNEL=="raw7", OWNER="sybase", GROUP="sybase", MODE="640"
KERNEL=="raw8", OWNER="sybase", GROUP="sybase", MODE="640"
KERNEL=="rawctl", MODE:="666"
ref : https://www.petersap.nl/SybaseWiki/index.php?title=Installation_guidelines_IQ_16
The new -su database server option to set the password for the utility database has been added on SAP IQ 16.1.
The default length for the MIN_PASSWORD_LENGTH database option is now 6 instead of 3 on SAP IQ 16.1.
Therefore, the password must be 6 characters or longer.
1. Start the utility database with -su database server option
C:\>start_iq -n utility -su Sap123
2. Connected the utility database
C:\>dbisql -c "uid=DBA;pwd=Sap123;eng=utility;dbn=utility_db" -nogui
(DBA)>
Example :
[sybase@COS7-Sybase16 IQ-16_1]$ start_iq -n utility -su Sap123
Starting server utility on COS7-Sybase16 at port (07/18 06:16:32)
Run Directory : /opt/iq/IQ-16_1
Server Executable : /opt/iq/IQ-16_1/bin64/iqsrv16
Server Output Log : /opt/iq/IQ-16_1/logfiles/utility.0002.srvlog
Server Version : 16.1.030.1031/sp03
Open Client Version : 16.0 SP02 PL05
User Parameters : '-n' 'utility' '-su' 'Sap123'
Default Parameters : -c 48m -gc 20 -gd all -gl all -gm 10 -gp 4096 -ti 4400 -gn 25
I. 07/18 06:16:34.243607 SAP IQ
I. 07/18 06:16:34.243870 Version 16.1
I. 07/18 06:16:34.243905 (64bit mode)
I. 07/18 06:16:34.243918 Copyright 1992-2017 by SAP AG or an SAP affiliate company. All rights reserved
I. 07/18 06:16:34.243935 Copyright (c) 2018 SAP SE or an SAP affiliate company.
I. 07/18 06:16:34.243948 All rights reserved.
I. 07/18 06:16:34.243960 Use of this software is governed by the SAP Software Use Rights Agreement.
I. 07/18 06:16:34.243972 Refer to http://www.sap.com/about/agreements.html.
I. 07/18 06:16:34.243984
I. 07/18 06:16:34.244003 Processors detected: 1 logical processor(s) on 1 core(s) on 1 physical processor(s)
I. 07/18 06:16:34.244024 This server is licensed to use: all logical processors in the system
I. 07/18 06:16:34.244038 Processors in use by server: 1 logical processor(s) on 1 core(s) on 1 physical processor(s)
I. 07/18 06:16:34.244072 Running Linux 3.10.0-862.6.3.el7.x86_64 #1 SMP Tue Jun 26 16:32:21 UTC 2018 on X86_64
I. 07/18 06:16:34.244088 Server built for X86_64 processor architecture
I. 07/18 06:16:34.247980 49400K of memory used for caching
I. 07/18 06:16:34.248035 Minimum cache size: 49344K, maximum cache size: 82072K
I. 07/18 06:16:34.248053 Using a maximum page size of 4096 bytes
I. 07/18 06:16:34.266425 Multiprogramming level: 25
I. 07/18 06:16:34.266494 Automatic tuning of multiprogramming level is disabled
=============================================================
IQ server starting with:
10 connections ( -gm )
12 cmd resources ( -iqgovern )
80 threads ( -iqmt )
512 Kb thread stack size ( -iqtss )
40960 Kb thread memory size ( -iqmt * -iqtss )
1 IQ number of cpus ( -iqnumbercpus )
0 MB maximum size of IQMSG file ( -iqmsgsz )
0 copies of IQMSG file archives ( -iqmsgnum )
64 MB maximum size of main buffer cache ( -iqmc )
64 MB maximum size of temp buffer cache ( -iqtc )
2048 MB maximum size of large memory pool ( -iqlm )
0 MB maximum size of heap memory ( -iqmem )
2048 MB maximum size of RLV memory ( -iqrlvmem )
=============================================================
I. 07/18 06:16:34.292251 Database server started at Wed Jul 18 2018 06:16
I. 07/18 06:16:34.293038 Trying to start SharedMemory link ...
I. 07/18 06:16:34.294489 SharedMemory link started successfully
I. 07/18 06:16:34.294529 Trying to start TCPIP link ...
I. 07/18 06:16:34.295459 Starting on port 2638
I. 07/18 06:16:39.297167 TCPIP link started successfully
I. 07/18 06:16:39.298590 Now accepting requests
New process id is 15955
Server started successfully
[sybase@COS7-Sybase16 IQ-16_1]$ dbisql -c "uid=DBA;pwd=Sap123;eng=utility;dbn=utility_db" -nogui
(DBA)>
(DBA)> create database 'NTNX.db1' transaction log on 'NTNX.log' message path 'NTNX.iqmsg' iq path '/dev/raw/raw1' iq size 100000 iq reserve 100000 temporary path '/dev/raw/raw5' temporary size 100000 temporary reserve 100000 dba user 'DBA' dba password 'nutanix/4u'
CHAR collation sequence: ISO_BINENG(CaseSensitivity=Respect)
CHAR character set encoding: ISO_8859-1:1987
NCHAR collation sequence: UCA(CaseSensitivity=UpperFirst;AccentSensitivity=Respect;PunctuationSensitivity=Primary)
NCHAR character set encoding: UTF-8
Database is not encrypted
Creating system tables
Creating system views
Setting option values
$ stop_iq -stop all
Checking system ...
The following 1 server(s) are owned by 'sybase'
## Owner PID Started CPU Time Additional Information
-- --------- ------- -------- -------- ------------------------------------
1: sybase 15955 06:16 00:00:09 SVR:utility DB:none PORT:
/opt/iq/IQ-16_1/bin64/iqsrv16 -n utility -su Sap123 -c 48m -gc 20 -gd all -gl all -gm 10 -gp 4096 -ti 4400 -gn 25 -o /opt/iq/IQ-16_1/logfiles/utili
--
Shutting down server (15955) ...
Checkpointing server (15955) ...
Server shutdown.
$ ls
bin64 DBACOCKPIT demo install IQ-16_1.csh IQ-16_1.sh java lib64 logfiles lsunload NTNX.db1 res samples scripts sdk shared Sysam tix unload
cat params.cfg
-n NTNX
-iqmc 4000
-iqtc 6000
$ start_iq @./params.cfg ./NTNX.db1
Starting server NTNX on COS7-Sybase16 at port (07/18 07:58:26)
Run Directory : /opt/iq/IQ-16_1
Server Executable : /opt/iq/IQ-16_1/bin64/iqsrv16
Server Output Log : /opt/iq/IQ-16_1/logfiles/NTNX.0001.srvlog
Server Version : 16.1.030.1031/sp03
Open Client Version : 16.0 SP02 PL05
User Parameters : '@./params.cfg' './NTNX.db1'
Default Parameters : -c 48m -gc 20 -gd all -gl all -gm 10 -gp 4096 -ti 4400 -gn 25
I. 07/18 07:58:28.771130 SAP IQ
I. 07/18 07:58:28.771577 Version 16.1
I. 07/18 07:58:28.771597 (64bit mode)
I. 07/18 07:58:28.771618 Copyright 1992-2017 by SAP AG or an SAP affiliate company. All rights reserved
I. 07/18 07:58:28.771635 Copyright (c) 2018 SAP SE or an SAP affiliate company.
I. 07/18 07:58:28.771655 All rights reserved.
I. 07/18 07:58:28.771681 Use of this software is governed by the SAP Software Use Rights Agreement.
I. 07/18 07:58:28.771708 Refer to http://www.sap.com/about/agreements.html.
I. 07/18 07:58:28.771722
I. 07/18 07:58:28.777292 Processors detected: 1 logical processor(s) on 1 core(s) on 1 physical processor(s)
I. 07/18 07:58:28.777329 This server is licensed to use: all logical processors in the system
I. 07/18 07:58:28.777346 Processors in use by server: 1 logical processor(s) on 1 core(s) on 1 physical processor(s)
I. 07/18 07:58:28.777372 Running Linux 3.10.0-862.6.3.el7.x86_64 #1 SMP Tue Jun 26 16:32:21 UTC 2018 on X86_64
I. 07/18 07:58:28.777388 Server built for X86_64 processor architecture
I. 07/18 07:58:28.787147 49400K of memory used for caching
I. 07/18 07:58:28.787206 Minimum cache size: 49344K, maximum cache size: 82072K
I. 07/18 07:58:28.787224 Using a maximum page size of 4096 bytes
I. 07/18 07:58:28.793341 Multiprogramming level: 25
I. 07/18 07:58:28.793383 Automatic tuning of multiprogramming level is disabled
=============================================================
IQ server starting with:
10 connections ( -gm )
12 cmd resources ( -iqgovern )
80 threads ( -iqmt )
512 Kb thread stack size ( -iqtss )
40960 Kb thread memory size ( -iqmt * -iqtss )
1 IQ number of cpus ( -iqnumbercpus )
0 MB maximum size of IQMSG file ( -iqmsgsz )
0 copies of IQMSG file archives ( -iqmsgnum )
4000 MB maximum size of main buffer cache ( -iqmc )
6000 MB maximum size of temp buffer cache ( -iqtc )
2048 MB maximum size of large memory pool ( -iqlm )
0 MB maximum size of heap memory ( -iqmem )
2048 MB maximum size of RLV memory ( -iqrlvmem )
=============================================================
I. 07/18 07:58:28.825113 Starting database "NTNX" (/opt/iq/IQ-16_1/NTNX.db1) at Wed Jul 18 2018 07:58
I. 07/18 07:58:29.162552 Transaction log: NTNX.log
I. 07/18 07:58:29.172084 Starting checkpoint of "NTNX" (NTNX.db1) at Wed Jul 18 2018 07:58
I. 07/18 07:58:29.303475 Finished checkpoint of "NTNX" (NTNX.db1) at Wed Jul 18 2018 07:58
I. 07/18 07:58:29.592054 Update previous log GUID to: 048328de-8a3e-11e8-8000-91bd89ff8212
Update current log GUID to :d4b35d6c-8a3e-11e8-8000-95b011f96f18
I. 07/18 07:58:29.603893 Database "NTNX" (NTNX.db1) started at Wed Jul 18 2018 07:58
I. 07/18 07:58:29.633423 IQ Server NTNX.
I. 07/18 07:58:29.634111 Starting checkpoint of "NTNX" (NTNX.db1) at Wed Jul 18 2018 07:58
I. 07/18 07:58:29.761163 Finished checkpoint of "NTNX" (NTNX.db1) at Wed Jul 18 2018 07:58
I. 07/18 07:58:29.762650 Database server started at Wed Jul 18 2018 07:58
I. 07/18 07:58:29.762999 Trying to start SharedMemory link ...
I. 07/18 07:58:29.765140 SharedMemory link started successfully
I. 07/18 07:58:29.765185 Trying to start TCPIP link ...
I. 07/18 07:58:29.771352 Starting on port 2638
I. 07/18 07:58:34.773170 TCPIP link started successfully
I. 07/18 07:58:34.774773 Now accepting requests
New process id is 16649
Server started successfully
$ dbisql -c "uid=DBA;pwd=nutanix/4u;eng=NTNX;dbn=NTNX" -nogui
o list processes :
(DBA)> sp_iqwho
ConnHandle IQconnID Userid ReqType IQCmdType BlockedOn BlockUserid IQCursors IQThreads IQIdle SAIdle TempTableSpaceKB TempWorkSpaceKB
-----------------------------------------------------------------------------------------------------------------------------------------------
5 48 DBA OPEN IQUTILITYOPENCURSOR 0 (NULL) 0 0 0 0 0 0
(1 rows)
o detailed connection properties
(DBA)> sp_iqshowpsexe ;
connectionid application userid iqgovern_priority max_query_time query_row_limit query_temp_space_limit max_statements max_cursors
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
5 HOST=COS7-Sybase16.1;EXE=/opt/iq/shared/SAPJRE-8_1_008_64BIT/bin/java; DBA MEDIUM 0 0 0 100 50
1000000004 MEDIUM 0 0 0 100 50
(2 rows)
o open transaction / versions
(DBA)> sp_iqtransaction ;
Name Userid TxnID CmtID VersionID State ConnHandle IQConnID MainTableKBCr MainTableKBDr TempTableKBCr TempTableKBDr TempWorkSpaceKB TxnCreateTime CursorCount SpCount SpNumber MPXServerName GlobalTxnID VersioningType Blocking BlockingTimeout
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_DBC_7f75d803f3c0 DBA 778 0 778 ACTIVE 5 48 0 0 0 0 0 2018-07-18 08:11:17.698 0 146 145 (NULL) 0 Table-level False 0
(1 rows)
o SQL running from a specific connection
(DBA)> sp_iqcontext 5
ConnOrCursor ConnHandle Name Userid numIQCursors IQthreads TxnID ConnOrCurCreateTime IQconnID IQGovernPriority CmdLine Attribute
-------------------------------------------------------------------------------------------------------------------------------------------------
CONNECTION 5 NTNX SYS 0 0 778 2018-07-18 08:10:48.0 48 2 call "sp_iqcontext"(5) NULL
(1 rows)
o To list the databases, per server
$ dblocate -d
SQL Anywhere Server Enumeration Utility Version 17.0.9.1031
Server Name Address Database(s)
----------- ------- -----------
ntnx cos7-sybase16:2638 NTNX
1 server found
(DBA)> sp_iqstatus
Name Value
-------------------------------------------------------------------------------------------------------------------------------------
SAP IQ (TM) Copyright (c) 1992-2016 by SAP AG or an SAP affiliate company. All rights reserved.
Version: 16.1.030.1031/10645/P/sp03/Linux/Linux64 - x86_64 - 3.10.0-327/64bit/2018-03-21 02:29:37
Time Now: 2018-07-18 08:41:18.772
Build Time: 2018-03-21 02:29:37
File Format: 23 on 03/18/1999
Server mode: IQ Server
Catalog Format: 2
Stored Procedure Revision: 1
Page Size: 131072/8192blksz/16bpp
Number of Main DB Files: 1
Main Store Out Of Space: N
Number of Cache Dbspace Files: 0
Number of Shared Temp DB Files: 0
Shared Temp Store Out Of Space: N
Number of Local Temp DB Files: 1
Local Temp Store Out Of Space: N
DB Blocks: 1-12800000 IQ_SYSTEM_MAIN
Local Temp Blocks: 1-12800000 IQ_SYSTEM_TEMP
Create Time: 2018-07-18 07:52:44.102
Update Time: 2018-07-18 07:52:44.102
Main IQ Buffers: 31869, 4000Mb
Temporary IQ Buffers: 47804, 6000Mb
Main IQ Blocks Used: 2560129 of 12672000, 20%=19Gb, Max Block#: 2560193
Cache Dbspace IQ Blocks Used: 0 of 0, 0%=0Mb, Max Block#: 0
Shared Temporary IQ Blocks Used: 0 of 0, 0%=0Mb, Max Block#: 0
Local Temporary IQ Blocks Used: 833 of 12672000, 0%=6Mb, Max Block#: 0
Main Reserved Blocks Available: 128000 of 128000, 100%=1000Mb
Shared Temporary Reserved Blocks Available: 0 of 0, 0%=0Mb
Local Temporary Reserved Blocks Available: 128000 of 128000, 100%=1000Mb
IQ Dynamic Memory: Current: 10181mb, Max: 10193mb
IQ Heap Memory: Current: 145mb, Max: 157mb, Limit: 0mb
Main IQ Buffers: Used: 52, Locked: 0
Temporary IQ Buffers: Used: 52, Locked: 0
Main IQ I/O: I: L689/P52 O: C0/D150/P125 D:0 C:100.0
Temporary IQ I/O: I: L925/P0 O: C52/D177/P152 D:0 C:100.0
Other Versions: 0 = 0Mb
Active Txn Versions: 0 = C:0Mb/D:0Mb
Last Full Backup ID: 0
Last Full Backup Time:
Last Backup ID: 0
Last Backup Type: None
Last Backup Time:
DB Updated: 0
Blocks in next ISF Backup: 0 Blocks: =0Mb
Blocks in next ISI Backup: 0 Blocks: =0Mb
IQ large memory space: 2048Mb
IQ large memory flexible percentage: 50
IQ large memory flexible used: 0Mb
IQ large memory inflexible percentage: 90
IQ large memory inflexible used: 0Mb
IQ large memory anti-starvation percentage: 50
DB File Encryption Status: OFF
RLV Status: RW
RLV memory limit (mb): 2048
RLV memory used (bytes): 0
RLV Log Buffers Allocated: 0
RLV Log Buffers Globally Free: 0
RLV Log Buffers Privately Free: 0
RLV Log Buffers In Use: 0
(59 rows)
(DBA)> alter dbspace IQ_SYSTEM_MAIN add file main_02 '/dev/raw/raw2' size 100000 MB reserve 100000 MB ;
(DBA)> alter dbspace IQ_SYSTEM_TEMP add file temp_02 '/dev/raw/raw6' size 100000 MB reserve 100000 MB ;
(DBA)> sp_iqfile;
DBSpaceName DBFileName Path SegmentType RWMode Online Usage DBFileSize Reserve StripeSize BlkTypes FirstBlk LastBlk OkToDrop servername mirrorLogicalFileName IsDASSharedFile
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
IQ_SYSTEM_MAIN IQ_SYSTEM_MAIN /dev/raw/raw1 MAIN RW T 21 97.7G 97.7G 1K 1H,2559968F,32D,128M 1 12800000 Y (NULL) (NULL) F
IQ_SYSTEM_MAIN main_02 /dev/raw/raw2 MAIN RW T 20 97.7G 97.7G 1K 1H,2559968F 26136000 38935999 Y (NULL) (NULL) F
IQ_SYSTEM_TEMP IQ_SYSTEM_TEMP /dev/raw/raw5 TEMPORARY RW T 1 97.7G 97.7G 1K 1H,832F,16A 1 12800000 N (NULL) (NULL) F
IQ_SYSTEM_TEMP temp_02 /dev/raw/raw6 TEMPORARY RW T 1 97.7G 97.7G 1K 1H,800F 26136000 38935999 Y (NULL) (NULL) F
(4 rows)
(DBA)> sp_iqdbspace;
DBSpaceName DBSpaceType Writable Online Usage TotalSize Reserve NumFiles NumRWFiles Stripingon StripeSize BlkTypes OkToDrop lsname is_dbspace_preallocated
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
IQ_SYSTEM_MAIN MAIN T T 21 195G 195G 2 2 T 1K 2H,5119936F,32D,128M N (NULL) T
IQ_SYSTEM_TEMP TEMPORARY T T 1 195G 195G 2 2 T 1K 2H,1632F,32A N (NULL) T
(2 rows)
create simplex db to convert to multiplex...
o start utility db and connct over dbisql
start_iq -n utility -su Sap123
dbisql -c "uid=DBA;pwd=Sap123;eng=utility;dbn=utility_db" -nogui
o create simplex db...
create database 'MPXTST.db1' transaction log on 'MPXTST.log' message path 'MPXTST.iqmsg' iq path '/dev/raw/raw1' iq size 100000 iq reserve 100000 temporary path '/dev/raw/raw5' temporary size 100000 temporary reserve 100000
dba user 'DBA' dba password 'nutanix/4u'
o stop utility db..
stop_iq -stop one
o start newly created simplex db..
$ start_iq @params.cfg -n mpxnode_c -x "tcpip{port=2763}" MPXTST.db1
$ dbisql -c "uid=DBA;pwd=nutanix/4u" -nogui
(DBA)>
(DBA)> connect
Connected to "null" on "MPX".
(DBA)> sp_iqwho
ConnHandle IQconnID Userid ReqType IQCmdType BlockedOn BlockUserid IQCursors IQThreads IQIdle SAIdle TempTableSpaceKB TempWorkSpaceKB
-----------------------------------------------------------------------------------------------------------------------------------------------
3 237 DBA OPEN IQUTILITYOPENCURSOR 0 (NULL) 0 0 0 0 0 0
(1 rows)
(DBA)> CREATE MULTIPLEX SERVER mpxnode_w1 DATABASE '/opt/iq/IQ-16_1/MPXTST.db1' HOST '10.68.64.131' PORT 2957 ROLE WRITER STATUS INCLUDED
CREATE MULTIPLEX SERVER mpxnode_w1 DATABASE '/opt/iq/IQ-16_1/MPXTST.db1' HOST '10.68.64.131:2957' ROLE WRITER STATUS INCLUDED
o by default IQ shuts down at this point - will then need to restart in multiplex mode ....
$ start_iq @params.cfg -n mpxnode_c -x "tcpip{port=2763}" MPXTST.db1
Starting server MPX on cos7-sybase16 at port 2763 (07/20 06:25:46)
Run Directory : /opt/iq/IQ-16_1
Server Executable : /opt/iq/IQ-16_1/bin64/iqsrv16
Server Output Log : /opt/iq/IQ-16_1/logfiles/MPX.0007.srvlog
Server Version : 16.1.030.1031/sp03
Open Client Version : 16.0 SP02 PL05
User Parameters : '@params.cfg' '-n' 'mpxnode_c' '-x' 'tcpip{port=2763}' 'MPXTST.db1'
Default Parameters : -c 48m -gc 20 -gd all -gl all -gm 10 -gp 4096 -ti 4400 -gn 25
I. 07/20 06:25:48.756906 SAP IQ
I. 07/20 06:25:48.757212 Version 16.1
I. 07/20 06:25:48.757230 (64bit mode)
I. 07/20 06:25:48.757243 Copyright 1992-2017 by SAP AG or an SAP affiliate company. All rights reserved
I. 07/20 06:25:48.757268 Copyright (c) 2018 SAP SE or an SAP affiliate company.
I. 07/20 06:25:48.757282 All rights reserved.
I. 07/20 06:25:48.757318 Use of this software is governed by the SAP Software Use Rights Agreement.
I. 07/20 06:25:48.757334 Refer to http://www.sap.com/about/agreements.html.
I. 07/20 06:25:48.757346
I. 07/20 06:25:48.757366 Processors detected: 1 logical processor(s) on 1 core(s) on 1 physical processor(s)
I. 07/20 06:25:48.757396 This server is licensed to use: all logical processors in the system
I. 07/20 06:25:48.757412 Processors in use by server: 1 logical processor(s) on 1 core(s) on 1 physical processor(s)
I. 07/20 06:25:48.757437 Running Linux 3.10.0-862.6.3.el7.x86_64 #1 SMP Tue Jun 26 16:32:21 UTC 2018 on X86_64
I. 07/20 06:25:48.757458 Server built for X86_64 processor architecture
I. 07/20 06:25:48.774910 49400K of memory used for caching
I. 07/20 06:25:48.775034 Minimum cache size: 49344K, maximum cache size: 82072K
I. 07/20 06:25:48.775053 Using a maximum page size of 4096 bytes
I. 07/20 06:25:48.782867 Multiprogramming level: 25
I. 07/20 06:25:48.782942 Automatic tuning of multiprogramming level is disabled
=============================================================
IQ server starting with:
10 connections ( -gm )
12 cmd resources ( -iqgovern )
80 threads ( -iqmt )
512 Kb thread stack size ( -iqtss )
40960 Kb thread memory size ( -iqmt * -iqtss )
1 IQ number of cpus ( -iqnumbercpus )
0 MB maximum size of IQMSG file ( -iqmsgsz )
0 copies of IQMSG file archives ( -iqmsgnum )
4000 MB maximum size of main buffer cache ( -iqmc )
6000 MB maximum size of temp buffer cache ( -iqtc )
2048 MB maximum size of large memory pool ( -iqlm )
0 MB maximum size of heap memory ( -iqmem )
2048 MB maximum size of RLV memory ( -iqrlvmem )
=============================================================
I. 07/20 06:25:48.843585 Starting database "MPXTST" (/opt/iq/IQ-16_1/MPXTST.db1) at Fri Jul 20 2018 06:25
I. 07/20 06:25:49.193019 Transaction log: MPXTST.log
I. 07/20 06:25:49.212011 Starting checkpoint of "MPXTST" (MPXTST.db1) at Fri Jul 20 2018 06:25
I. 07/20 06:25:49.340704 Finished checkpoint of "MPXTST" (MPXTST.db1) at Fri Jul 20 2018 06:25
I. 07/20 06:25:49.932833 Update previous log GUID to: 8be16c36-8bc0-11e8-8000-e09e60f5c64c
Update current log GUID to :37b5f2cc-8bc4-11e8-8000-9fd6c3084e74
I. 07/20 06:25:49.946341 Database "MPXTST" (MPXTST.db1) started at Fri Jul 20 2018 06:25
I. 07/20 06:25:50.208725 IQ Multiplex Coordinator Server MPX.
I. 07/20 06:25:50.211047 TLS is not enabled for INC RPC.
IQ OpenDatabase, checkpointblock: 2560034, recover: 1
IQ OpenDatabase, checkpointblock: 2560050, recover: 0
Using licenses from: /usr/local/flexlm/licenses/license.dat
Checked out graced license for 1 IQ_CORE (2018.0321) will expire Thu 18 Oct 2018 01:00:00 AM EDT.
Failed to obtain license(s) for IQ_CORE feature from license file(s) or server(s).
Cannot find license file.
The license files (or license server system network addresses) attempted are listed below.
License feature name: IQ_CORE
License filename: /opt/iq/SYSAM-2_0/licenses:/opt/iq/IQ-16_1
License search path: /opt/iq/SYSAM-2_0/licenses:/opt/iq/IQ-16_1
FlexNet Licensing error:-1,359. System Error: 2 "No such file or directory"
WARNING: IQ functionality that requires the IQ_CORE license will be disabled on Thu 18 Oct 2018 01:00:00 AM EDT, unless a suitable IQ_CORE license is obtained before that date.
Started DAS listener on 0.0.0.0:40655
I. 07/20 06:25:50.213604 Starting checkpoint of "MPXTST" (MPXTST.db1) at Fri Jul 20 2018 06:25
I. 07/20 06:25:50.335707 Finished checkpoint of "MPXTST" (MPXTST.db1) at Fri Jul 20 2018 06:25
I. 07/20 06:25:50.340226 Database server started at Fri Jul 20 2018 06:25
I. 07/20 06:25:50.340521 Trying to start SharedMemory link ...
I. 07/20 06:25:50.346717 SharedMemory link started successfully
I. 07/20 06:25:50.346781 Trying to start TCPIP link ...
I. 07/20 06:25:50.360435 Starting on port 2763
I. 07/20 06:25:50.374105 WARNING : Multiplex environment incorrect for this server
I. 07/20 06:25:50.374202 Please connect and run procedure sp_iqmpxvalidate for help
I. 07/20 06:25:55.363165 TCPIP link started successfully
I. 07/20 06:25:55.364641 Now accepting requests
New process id is 26530
Server started successfully
(DBA)> sp_iqmpxvalidate
Messages
-----------------------------------------------
WARNING: Server mpxnode_w1 has no Temp dbspaces
(1 rows)
o create a copy of db file from coordinator on secondary - ie run this on the secondary
dbbackup -y -p -d -c "uid=DBA;pwd=nutanix/4u;host=10.68.64.190:2763;dbn=MPXTST" .
SQL Anywhere Backup Utility Version 17.0.9.1031
(922 of estimated 922 pages, 100% complete)
Database backup completed
[sybase@cos7-sybase16 iq16.1]$ ls
charsets collate IQ-16_1 IQ.sh jre locales MPXTST.db1 shared SYBASE.env SYBASE.sh SYSAM-2_0
COCKPIT-4 config IQ.csh jConnect-16_0 jutils-3_0 log OCS-16_0 SYBASE.csh Sybase_Install_Registry sybuninstall
o reset the tranasction log - remove old/stale tx log first .... rm -rf MPXTST.log....:
$ dblog -r -t mpxtest.log mpxtst.db1
SQL Anywhere Transaction Log Utility Version 17.0.9.1031
Database "mpxtst.db1" not found
[sybase@cos7-sybase16 iq16.1]$ dblog -r -t MPXTST.log MPXTST.db1
SQL Anywhere Transaction Log Utility Version 17.0.9.1031
"MPXTST.db1" was using log file "MPXTST.log"
Transaction log filename not changed
"MPXTST.db1" was using no log mirror file
Transaction log mirror filename not changed
Transaction log starting offset is 0001386119
Transaction log current relative offset is 0000092345
o create params.cfg ...
$ cat params.cfg
-iqmc 4000
-iqtc 6000
o start secondary server
start_iq -STARTDIR . @params.cfg -n mpxnode_w1 -x "tcpip{port=2957}" ./MPXTST.db1
o connect to secondary server
$ dbisql -c "uid=DBA;pwd=nutanix/4u;eng=mpxnode_w1;links=tcpip{port=2957}" -nogui
(DBA)>
o Add the temporary dbfile
(DBA)> sp_iqdbspace;
DBSpaceName DBSpaceType Writable Online Usage TotalSize Reserve NumFiles NumRWFiles Stripingon StripeSize BlkTypes OkToDrop lsname is_dbspace_preallocated
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
IQ_SYSTEM_MAIN MAIN T T 21 97.7G 97.7G 1 1 T 1K 1H,2559968F,32D,19A,128M N (NULL) T
IQ_SYSTEM_TEMP TEMPORARY T T 1 97.7G 97.7G 1 1 T 1K 1H,832F,16A N (NULL) T
(2 rows)
(DBA)> ALTER DBSPACE IQ_SYSTEM_TEMP ADD FILE mpxnode_w1_temp '/dev/raw/raw6' SIZE 100000
(DBA)> sp_iqdbspace;
DBSpaceName DBSpaceType Writable Online Usage TotalSize Reserve NumFiles NumRWFiles Stripingon StripeSize BlkTypes OkToDrop lsname is_dbspace_preallocated
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
IQ_SYSTEM_MAIN MAIN T T 21 97.7G 97.7G 1 1 T 1K 1H,2559968F,32D,19A,128M N (NULL) T
IQ_SYSTEM_TEMP TEMPORARY T T 1 195G 97.7G 2 2 T 1K 2H,1248F,16A N (NULL) T
(2 rows)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment