cancel
Showing results for 
Search instead for 
Did you mean: 

Can we create Proxy table in ASE for IQ table

Former Member
0 Kudos

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

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

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 #

Former Member
0 Kudos

how much need to be increase ..

markmumy
Advisor
Advisor
0 Kudos

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

0 Kudos

nprocs of 1536 is too low. What is the issue with setting it unlimited? or set it to 64K. How many CPUs on the box? the threads created by an IQ server will depend upon number of CPUs, number of User connections configured etc.

Regards

Shashi

Former Member
0 Kudos

Thanks All . after increase nproc ..it resolved the issue .

Below the changes we did :

ulimt :

nproc                          (-u)  7712

cat /etc/security/limits.conf

sybase          soft    nproc           7712

sybase          hard    nproc           16384

Regards

Ajay Pandey

Answers (6)

Answers (6)

Former Member
0 Kudos

Sorry it was port issue .. it should be 2638 ....

I can connect now using isql .

Thanks

Ajay Pandey

Former Member
0 Kudos

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

Former Member
0 Kudos

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

Former Member
0 Kudos

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

c_baker
Employee
Employee
0 Kudos

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

markmumy
Advisor
Advisor
0 Kudos

What OS and release level?  The kernel level is not as important as the OS (SLES or RHEL) and the version.  Did you follow our installation guides about changing OS parameters and limits?

Mark

c_baker
Employee
Employee
0 Kudos

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

0 Kudos

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

Former Member
0 Kudos

how much need to be increase ..  ?

c_baker
Employee
Employee
0 Kudos

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

c_baker
Employee
Employee
0 Kudos

BTW, if you still need to use IQ at this point, then you could use DLM / DWF on HANA with IQ or Extended Storage.

Chris

markmumy
Advisor
Advisor
0 Kudos

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:

  • Use of # or temp tables
  • SELECT INTO
  • SQL that cannot be translated into IQ due to engine differences (GROUP BY ALL, subquery in the select list, etc)
  • Joins between IQ and non-IQ tables

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

markmumy
Advisor
Advisor
0 Kudos

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.

Using IQ functionality in ASE

The section on adding the IQ server is about midway down.

Mark

Former Member
0 Kudos

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

markmumy
Advisor
Advisor
0 Kudos

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.

SyBooks Online

Hopefully that helps.

Mark