This website is not affiliated with, sponsored by, or approved by SAP AG.

Creating a temporary table and index...

Basis (Basis Technology Modules: Basis Component/System Administration, GUIs)

Moderators: Snowy, thx4allthefish

Creating a temporary table and index...

Postby Dr Sidewalk » Fri Jul 07, 2006 8:59 am

I am trying to find out whether it's possible to create a transparent table with primary index, populate it with data, use it, and then drop it (including index), all via an ABAP program. I know that authorisation could be an issue but does any Basis person know how to do this ?.

I vaguely remember seeing some kind of FM to perform this but can't rememeber which ones. Just had a quick look and can see 'WUSL_CREATE_TRANSPARENT_TABLE' but that does not seem to do very much.

Your help would be appreciated.

Dr Sidewalk
"In the middle of difficulty lies opportunity" - Albert Einstein
"Money isn't everything in life, unless you don't have it"
"Fail to plan, plan to fail"
"Success is a journey, not a destination."
Dr Sidewalk
 
Posts: 3531
Joined: Mon Oct 21, 2002 11:06 am
Location: Everywhere...

Postby Snowy » Fri Jul 07, 2006 11:27 am

I do not think that Basis folks can help you. We have no idea on the usage of Function modules.

What I can say is that authorizations is not an issue if you do not call Security-related function checks.

But why do you want to create a drop a table? Is there a way that you could create it once and never delete it? Why not add a field with client and username and add the data you need and delete those new entries when finished?
SapFans Moderator

Search: http://www.sapfans.com/forums/search.php
Notes: http://service.sap.com/notes
Help: http://help.sap.com
Rules: http://www.sapfans.com/forums/viewtopic.php?t=344127
Snowy
 
Posts: 28767
Joined: Mon Oct 21, 2002 2:33 pm
Location: 3.1415926535

Postby miataboy » Fri Jul 07, 2006 2:23 pm

I would create a new tablespace, create the normal table there, add data sort it etc, then drop the table + tablespace. Or you could create the table in normal psaptabd tablespace and once you're done , truncate it at Oracle level. (supposing you had Oracle) That way you reclaim the space used up by it.
miataboy
 
Posts: 500
Joined: Thu Jun 05, 2003 12:22 pm
Location: 180mph, you're in my rearview mirror..

Here goes...

Postby Dr Sidewalk » Fri Jul 14, 2006 5:34 am

Here goes then....

At the moment we have a situation whereby using a FAEI (FOR ALL ENTRIES IN) clause within our SQL statement causes severe performances issues, using DB2. SAP themselves have investigated this and have recommended that we split the itab used by the FAEI clause into blocks of between 180-200 records. Of course this is crazy when you have an itab with 10,000+ records in it because we could be calling the SQL statement hundreds of times.
We have therefore implemented another solution which seems to work with remarkable improvements in performance. What we do is to insert the data that would normally exist in the itab and would be used by the FAEI cluase into a transparent table, then simple join to this using the SQL. Of course the transparent table being used has to be of the correct format.

We have a table called ZBC_FAEI which has the following format:

MANDT
SESSION_KEY CHAR 27 "(Username, Date, time,Processor ID.)
MATNR_VBELN CHAR 18
POSNR NUMC 6

The Session_key field uses the long length for time so it's down to the nearest micro/millisend or whatever is lower. It is defined just once at the start of execution. The table able above was built specifically so that we could replace the FAEI clauses in SQL statements which used either the material or sales order number and line number.

Therefore, the MATNR_VBELN field can be used to hold either a material number or sales document number.

All fields are keys and are non-null.

Having this table in place we can now replace code similar to that shown below:

Pseudo code shown....

Code: Select all
LOOP through blocks of 180 records from original source itab and assign to itab2.

SELECT ....
   APPENDING TABLE ...
   FROM tablex
   FOR ALL ENTRIES IN itab2
WHERE vbeln EQ itab2-vbeln
     AND posnr EQ itab2-posnr.

ENDLOOP.


This can now be replaced with...


Code: Select all
Insert all itab entries into the ZBC_FAEI transparent table.

SELECT ....
  INTO TABLE ....
  FROM zbc_faei AS A
  INNER join tablex AS B ON b~vbeln = A~Vbeln
                              AND B~posnr = A~posnr
WHERE a~session_key EQ g_sessionkey (Global variable)
   AND b~....

Delete all entries matching session_key from ZBC_FAEI table


Ideally I would want the equivalent of the ZBC_FAEI table to be created dynamically, and hence my question. At present we can only replace SQL statements that use the FAEI clause that access eithe material number (MATNE) or Sales order number (VBELN) and item line numeber (POSNR).

If we had the option of creating a transparent table and primary index we could optimise all of the code which has SQL statements using the FAEI clause.

Dr Sidewalk
"In the middle of difficulty lies opportunity" - Albert Einstein
"Money isn't everything in life, unless you don't have it"
"Fail to plan, plan to fail"
"Success is a journey, not a destination."
Dr Sidewalk
 
Posts: 3531
Joined: Mon Oct 21, 2002 11:06 am
Location: Everywhere...

Postby snmsee » Fri Jul 14, 2006 6:55 am

Thanks Dr Sidewalk. Great info.
snmsee
 
Posts: 3583
Joined: Thu Mar 09, 2006 9:09 am

Further questions...

Postby Dr Sidewalk » Wed Jul 19, 2006 3:21 am

Regarding my initial question. I think it's possible to create a table possibly using Native SQL to call Database statements. The same it also probably true for dropping the table when compete.

So, I can generate a random table name using one of the SAP FM's and pass this into the Native SQL statement along with the table structure and key information. But, using Native SQL statements would SAP's data dictionary get updated?. If not I would then also have to use Native SQL to populate and access the table, which is something I would prefer not to do.

Anyone?.

Dr Sidewalk
"In the middle of difficulty lies opportunity" - Albert Einstein
"Money isn't everything in life, unless you don't have it"
"Fail to plan, plan to fail"
"Success is a journey, not a destination."
Dr Sidewalk
 
Posts: 3531
Joined: Mon Oct 21, 2002 11:06 am
Location: Everywhere...

I'm not letting this go...

Postby Dr Sidewalk » Mon Jul 24, 2006 10:04 am

I'm not letting this one go.

I can now create a table and index using Native SQL and can even drop it when fininshed with. If I do this I assume that there'll be no SAP dictionary object and therefore and standard SAP SQL code attempting to access data in the newly created table probably will fail.


Is there a method to update the SAP data dictionary for such purposes. Even though I'm likely to drop the table once I've finished with it ?.

Anyone.

Dr Sidewalk
"In the middle of difficulty lies opportunity" - Albert Einstein
"Money isn't everything in life, unless you don't have it"
"Fail to plan, plan to fail"
"Success is a journey, not a destination."
Dr Sidewalk
 
Posts: 3531
Joined: Mon Oct 21, 2002 11:06 am
Location: Everywhere...

Postby MattG » Mon Jul 24, 2006 11:41 am

Could you just insert the table definition into the DD* tables at the start of processing. Thus creating the table in dictionary and the database. Then all you need to do is load the database, use your join, and then delete the table entries from your Z table, and the Z-table definition from the DD* tables.

Look at the INSERT and GENERATE commands.


Beware of using Generate people may realise that you can create whatever code you like in production execute it and there is no trace anywhere in the system to see what or how anything was changed.
MattG.
Image Search SAPfans
MattG
 
Posts: 809
Joined: Wed Oct 23, 2002 2:06 pm
Location: England

Yeah

Postby Dr Sidewalk » Tue Jul 25, 2006 3:47 am

MattG,

Yeah, I thought of that, but not so sure that will work. I have bounced this question from our Basis team in the US.

Reagrds

Dr Sidewalk
"In the middle of difficulty lies opportunity" - Albert Einstein
"Money isn't everything in life, unless you don't have it"
"Fail to plan, plan to fail"
"Success is a journey, not a destination."
Dr Sidewalk
 
Posts: 3531
Joined: Mon Oct 21, 2002 11:06 am
Location: Everywhere...

Postby KBM555866 » Tue Jul 25, 2006 2:10 pm

Try using these FMs

DD_CREATE_TABLE
DD_EXIST_TABLE
DD_DROP_TABLE
DD_DELETE_TABLE

- Mohan
Thanks

Mohan
KBM555866
 
Posts: 90
Joined: Tue Oct 05, 2004 7:01 pm
Location: Connecticut

Re: Creating a temporary table and index...

Postby englmaih » Wed Aug 27, 2014 2:44 am

Well, a little bit out of date now ...

I know about the issue and I am sick of it. You have two choices.

  1. Switch to SAP HANA, where they finally implemented that stuff.
  2. Forget about temporary tables. Create normal tables instead with CLIENT for convenience and a SY_UUID column. Fetch a UUID first (cl_system_uuid=>create...) which replaces creating the temporary table. Insert your records into the normal table marking them by the UUID. Delete the records by the UUID in the end.
englmaih
 
Posts: 1
Joined: Wed Aug 27, 2014 2:22 am

Re: Creating a temporary table and index...

Postby blueteeth » Mon Sep 01, 2014 3:24 pm

I missed this.

Disk space is cheap. Couple of cents per GB. Okay, Enterprise class disk space may be expensive...what...one dollar or two dollar per GB? 200 GB disk space?

BT
blueteeth
 
Posts: 499
Joined: Sat Apr 05, 2008 12:22 pm

Re: Creating a temporary table and index...

Postby Baz » Tue Sep 02, 2014 2:57 am

locked! hopefully it is still not an issue after 8 years....
Baz

AsPiRiNg tUlY iDiOt Image

http://www.catb.org/~esr/faqs/smart-questions.html

Image

check out my Podcasts http://dj-baz.podomatic.com
Baz
 
Posts: 4736
Joined: Fri Nov 08, 2002 5:54 am
Location: He's out there! somewhere!!!!


Return to Basis

Who is online

Users browsing this forum: Google [Bot] and 4 guests



cron


loading...


This website is not affiliated with, sponsored by, or approved by SAP AG.