0

As per sybase documentation for creating global temp tables.

Which says create a global temp table in tempdb. So that it can be shared by among different process.

Session 1: Here I create the global temp table through a proc.

Create procedure testglobaltemp as begin create table tempdb..tstglobal (id int , username varchar(8))

insert into tempdb..tstglobal select 1 , 'user1' end

create procedure accessglobaltemp as begin exec testglobaltemp select * from tempdb.tstglobal end

Everything works here as expected.

Session 2: (on a different instance) I run the same select * from tempdb.tstglobal it gives me the same result which is also expected.

Now I disconnect session 1 as per the documentation as soon I disconnect from session 1 the table should be dropped. But on session 2 i am still seeing data and query is working fine until or unless I dont drop the table explicitly.

Questions: I have 2 SP's say SP1 and SP2. I want to invoke them parallely using a third SP say SPCaller. is this possible in sybase 15.7

Thanks in advance.

Link : http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc32300.1570/html/sqlug/title.htm

SMor
  • 2,830
  • 4
  • 11
  • 14
Divyaansh Bajpai
  • 232
  • 1
  • 8
  • Not related to SQL Server - tag removed. I highly doubt Sybase has any direct support for parallel statement execution. – SMor Jun 27 '20 at 12:28
  • 1) 'global temporary tables' are not available in ASE 15.7; 2) `create table tempdb..tstglobal` creates a **permanent** table in the `tempdb` db (just like creating a (permanent) table in any user-defined database) which will exist until explicitly dropped or the ASE instance restarted (when tempdb is rebulit from scratch); 3) subject mentions passing result set between procs but there's no mention of this requirement in your description; 4) calling procs in parallel, while not doable *directly* in ASE, should be a separate question (with details on what you're trying to attempt) – markp-fuso Jun 27 '20 at 13:00
  • Thanks markup-fuso i had the similar understanding.The reason I asked this was because I read somewhere result set from SP was stored in temp tables with syntax like insert #tmp execute testglobaltemp but his wasnt working in 15.7. I wanted pass results set among procedures like oracle collections which ultimately lead towards taking a GTT approach. Do you have any suggestions for this. – Divyaansh Bajpai Jun 27 '20 at 13:13
  • `#tstglobal` is a temp table only accessible by the current process/spid; `tstglobal` is a permanent table that can be accessed by any process/spid that has the necessary permissions; `global temporary tables` are available in a later version of ASE 16 but likely overkill if a normal, permanent table (with the correct permissions) will suffice;if a normal, permanent table will not work for your situation ... you'll need to provide more details on what it is you're trying to accomplish – markp-fuso Jun 27 '20 at 13:40
  • So intention here is to prepare data at the beginning of my job which would be then used by subsequent jobs. Flow Job1(Creates some globaldata(Cache)) --> Job2,Job3 (Produces its own data using job1 data)-->Job4 would have collected this(in a GTT) and return complete data. I was trying to follow a more parallel approach here.With one GTT I am trying to create data cache and other trying to hold the result set of each subsequent jobs and return as one common result. – Divyaansh Bajpai Jun 27 '20 at 13:54
  • I still don't understand the focus on (global) temporary tables; sounds like you just need a plain, normal table that multiple processes/jobs can access; – markp-fuso Jun 27 '20 at 14:04
  • Yes intention was not to create to a physical table for intermediary processing was looking towards more in memory solution. – Divyaansh Bajpai Jun 27 '20 at 14:07
  • what's the expected benefit of a 'in memory solution'? why not create a physical table? create in tempdb, or if DBA doesn't want to worry about cleaning up tempdb, then have DBA create another temporary db for your sole use – markp-fuso Jun 27 '20 at 14:11
  • temp tables do place a strain on the temp_db, a common storage area which is accessed by other sessions in the db. i echo @markp-fuso suggestion, having a regular table, that does a truncate + load process each time the Job runs would be a better alternative. – George Joseph Jun 28 '20 at 02:50
  • for the question of parallelizing workflow, if you have an ETL tool such as SSIS, Informatica, it can easily be set up. Supposing you do not have then consider the user of running the jobs as a background task http://infocenter.sybase.com/help/topic/com.sybase.dc20001_1251/html/jsusers/BAJDEEFD.htm – George Joseph Jun 28 '20 at 03:00
  • Makes sense.! Got confused with info mentioned on infocenter.sybase Adaptive Server Enterprise 15.7 SP100 > Transact-SQL Users Guide 15.7 > Creating Databases and Tables > Creating tables .. Since we use 15.7 hence the confusion of tempdb. Looks like I can do it running two separate threads of SP each creating its on #tmp table and invoking other SP from inside this SP which creates this #tmp table so that its available or all. Thanks for sharing your thoughts. – Divyaansh Bajpai Jun 28 '20 at 07:03

0 Answers0