on 06-28-2016 8:41 PM
Hi ,
We are planning to move reporting ASE server to IQ for better performance .
Question : Can we create proxy table in Sybase ASE for IQ table .
What we are trying to achieve :
1) Migrate All tables /data to IQ Servers
2) Keep business logic in sybase (Store Proc etc)
3) Create proxy table in sybase which will point to IQ tables
4) Execute proc in Sybase which will extract the data internally from IQ .
So it will not required to rewrite business logic in IQ as we will be using ASE proc which is already there in ASE . No change in Application .
Appreciate your response .
Regards
Ajay Pandey
uxuselkg167:/sybase/IQ # ulimit -a
address space limit (Kibytes) (-M) unlimited
core file size (blocks) (-c) 0
cpu time (seconds) (-t) unlimited
data size (Kibytes) (-d) unlimited
file size (blocks) (-f) unlimited
locks (-x) unlimited
locked address space (Kibytes) (-l) 29696000
message queue size (Kibytes) (-q) 800
nice (-e) 0
nofile (-n) 4096
nproc (-u) 1536
pipe buffer size (bytes) (-p) 4096
max memory size (Kibytes) (-m) unlimited
rtprio (-r) 0
socket buffer size (bytes) (-b) 4096
sigpend (-i) 257445
stack size (Kibytes) (-s) 10240
swap size (Kibytes) (-w) not supported
threads (-T) not supported
process size (Kibytes) (-v) unlimited
uxuselkg167:/sybase/IQ #
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Kindly read the installation guide and release bulletin. This is covered in the known issues section of the release bulletin. There are also other kernel settings that you need to make and patches that must be installed.
Installation and Configuration - SAP IQ Release Bulletin - SAP Library
Set it to a high value or unlimited. Our docs mention 7712 as a value in one workaround or removing the limit altogether in another workaround.
Mark
Sorry it was port issue .. it should be 2638 ....
I can connect now using isql .
Thanks
Ajay Pandey
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
One more help
Now i can connect use utility db using dbisql ..
dbisql -nogui -c "uid=dba;pwd=sql;eng=utildb;dbn=utility_db;links=tcpip{host=uxuselkg167;port=6348}"
but not able to connect using isql ..
Added entry in interfaces ..
uxuselkg167:/sybase/IQ # cat interfaces
utility_db
master tcp ether uxuselkg167 6348
query tcp ether uxuselkg167 6348
uxuselkg167:/sybase/IQ # isql -Udba -Sutility_db -w2000 -I interfaces -Psql
CT-LIBRARY error:
ct_connect(): network packet layer: internal net library error: Net-Lib protocol driver call to connect two endpoints failed
uxuselkg167:/sybase/IQ #
is anything missing ..?
Regards
Ajay Pandey
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
No other IQ server is running .. This is entirely new installation on linux box .
I installed IQ with now error . So just tried to start Util db toi can create iq database etc .
It is starting utildb successfully . after that it is not allowing me to do anything with error that i reported . Looks some thread issue ..
OS :
uxuselkg167:/sybase/IQ/IQ-16_0/logfiles # uname -a
Linux uxuselkg167 2.6.32-573.7.1.el6.x86_64 #1 SMP Thu Sep 10 13:42:16 EDT 2015 x86_64 x86_64 x86_64 GNU/Linux
cat /etc/security/limits.conf
sybase soft memlock 29696000
sybase hard memlock 29696000
uxuselkg167:/sybase/IQ/IQ-16_0 # $IQDIR16/bin64/start_iq -v2
16.0.0.2014
SAP IQ/16.0.110.2014/10214/P/sp11/Enterprise Linux64 - x86_64 - 2.6.18-194.el5/64bit/2015-11-21 01:29:07
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thanks Mark /Chris . Actually HANA is very expensive . So we are planning to use IQ . as u suggested will try to create proc in IQ .
I just installed IQ on Linux XXXX 2.6.32-573.7.1.el6.x86_64
but when trying to start utildb .. it is starting and giving error .. then do not allow me to do anything
start_iq -n utililty_db
fork: retry: Resource temporarily unavailable
Resource temporarily unavailable
Do i need to changes something on OS ? Appreciate your help .
Regards
Ajay Pandey
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You have named the server, but set no other parameters.
The easiest way to start an IQ server is:
start_iq @params.cfg database.db
or
start_iq @params.cfg
Either method will provide a utility_db to connect to the server for issuing commands to the server.
The second method will provide you only the utility_db (you can set -n to anything you like, even in the params.cfg file, but the issue in your case might be the port assignment - default is 2638 and you could have an SQLA/IQ on there already).
The advantage of the second method, is that you can set the memory (-iqmc, -iqtc, etc) correctly up front in the params.cfg, so the initial creation of your database will be much faster (for the IQ devices). Once created, you can stop the server and restart with the first method to continue altering your database.
Chris
In addition to the docs, I would suggest you follow the Best Practices and Sizing Guides:
http://wiki.scn.sap.com/wiki/display/SYBIQ/SAP+IQ+Best+Practices+and+Hardware+Sizing+Guide
Chris
On Linux you need to increase limit for number of procs can be started by the user. Since IQ is multi-threaded application, and Linux will use proc structures for threads you need to increase those.
Change the values in /etc/security/limits.conf file and increasing the hard/soft proc. You can check those values from "ulimit -a" output
The big issue with this is the translation of the ASE T-SQL to IQ SQL. For the most part CIS is smart enough to translate and push down as much as possible of the SQL to IQ, but the use of TEMP tables in the SP may cause tables to be fully retrieved into ASE for manipulation, etc, resulting in a performance impact on IQ due to 'table scans' and network performance.
The solution you are looking for has already been engineered (including TEMP table pushdown) as A4A or AFA (HANA Accelerator for ASE) using HANA instead of IQ.
This solution allows for an ASE to be replicated to HANA using SRS (RTL) and a proxy ASE to point to HANA for the proxy tables. Views and SPs are installed in the proxy ASE and ALL DML possible, including TEMP tables used in SPs, is pushed to HANA for resolution. Translations are made as necessary of the ASE SQL to map correctly to HANA functions and datatypes, so as much as possible, only the final results are actually sent back to the ASE. The complete SP is carved up and sent to HANA as SQL.
Chris
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Chris brings up really good points that you should be aware of. You may be going down a path that does not allow you to realize the full potential of IQ and could actually hurt performance. There are so many variations and possibilities that on the extreme side you could see very bad performance.
In your procedures, the things to look for that would possible cause poor performance are things like:
These, and more, will cause ASE to break up the statement so that it can get the data it needs from IQ, load it into ASE work space (tempdb), then perform more work in ASE. The amount of data that comes back is a huge factor for performance. Also, you may find that tempdb needs to be increased to handle this workload.
A good test for this is to simply take your ASE procedure and compile it in IQ. If it won't compile and run due to syntax errors or different functionality, then you will likely have ASE breaking up the code to do some work in IQ and some in ASE. Then again, if the procedure runs in IQ without issue, then why not move the procedure completely into IQ so that you are guaranteed of good performance.
Just some things to think about.
Mark
Yes, absolutely. Look at the create server syntax as well as create table or create proxy_table.
Start with Robs page in connecting IQ and ASE.
The section on adding the IQ server is about midway down.
Mark
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thanks Mark but Robs example is to create proxy in IQ to access ASE table.
My requirement is in otherway ..
My Requirement is ( Physical table reside in IQ) and Proxy in ASE (which should point to IQ server) .
Can u give me link to download evaluation version IQ16 (latest) . When i am going for download it showing only IQCLIENTODBC etc ..
Regards
Ajay Pandey
Midway down Rob has this:
We've set up the IQ side now. The remaining part is to configure ASE so that we can kick off the stored procedure iq_proc_for_ase above, which resides in IQ:
-- run these steps in ASE (run as 'sa' user): sp_configure 'enable cis', 1 go -- if it was not already set to 1, shutdown & restart ASE first sp_configure 'cis rpc handling', 1 go -- now disconnect & reconnect the session -- create mapping to remote IQ server : sp_addserver MY_IQ, ASIQ, 'myiqhost:2639' go -- create externlogin: sp_addexternlogin MY_IQ, my_ase_login, my_iq_login, my_iq_passwd go -- test the connection to IQ: connect to MY_IQ go select @@version -- should show the IQ version go disconnect go -- run as user 'my_ase_login': test executing the IQ stored procedure -- from within ASE: exec MY_IQ...iq_proc_for_ase 6 go
Once that's done, use the create proxy_table command in ASE using that server you just defined and point ASE to IQ.
Hopefully that helps.
Mark
User | Count |
---|---|
84 | |
25 | |
12 | |
9 | |
6 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.