Page 1 of 1

Creating a temporary table and index...

Posted: Fri Jul 07, 2006 8:59 am
by Dr Sidewalk
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

Posted: Fri Jul 07, 2006 11:27 am
by Snowy
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?

Posted: Fri Jul 07, 2006 2:23 pm
by miataboy
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.

Here goes...

Posted: Fri Jul 14, 2006 5:34 am
by Dr Sidewalk
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

Posted: Fri Jul 14, 2006 6:55 am
by snmsee
Thanks Dr Sidewalk. Great info.

Further questions...

Posted: Wed Jul 19, 2006 3:21 am
by Dr Sidewalk
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

I'm not letting this go...

Posted: Mon Jul 24, 2006 10:04 am
by Dr Sidewalk
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

Posted: Mon Jul 24, 2006 11:41 am
by MattG
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.

Yeah

Posted: Tue Jul 25, 2006 3:47 am
by Dr Sidewalk
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

Posted: Tue Jul 25, 2006 2:10 pm
by KBM555866
Try using these FMs

DD_CREATE_TABLE
DD_EXIST_TABLE
DD_DROP_TABLE
DD_DELETE_TABLE

- Mohan

Re: Creating a temporary table and index...

Posted: Wed Aug 27, 2014 2:44 am
by englmaih
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.

Re: Creating a temporary table and index...

Posted: Mon Sep 01, 2014 3:24 pm
by blueteeth
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

Re: Creating a temporary table and index...

Posted: Tue Sep 02, 2014 2:57 am
by Baz
locked! hopefully it is still not an issue after 8 years....