Truncate Table 'function'?

DBohner-(db)

Legendary Poster
Truncate Table \'function\'?

Is there a Truncate Table function?

We have several work tables that could / can get over half a billion records. When we go to clear Delete from them - it can take 30 minutes. If we could just execute 'Truncate Table'... it would take moments...


Also, is there a 'template' function that could be used to run Native SQL statements? I have a couple complex Mass-Insert/Grouped By Sum... queries - and I'd rather run them internally - than by executing an external script... If there is a function I could use as a template, passing a statement into - that would be grand!

(db)
 
Re: Truncate Table \'function\'?

Dan,

Not that I've ever seen.
 
Re: Truncate Table \'function\'?

There is an API called JDB_DropTable in B9800200. To see how it is used look at the Table Operations tab of P9860A/W9860AL.
 
Re: Truncate Table \'function\'?

A C function using JDB_DeleteTable with 0 keys and no set selection will perform a truncate table.

See the standard function B74B0250.

Here is the trimmed down debug.log from calling the function from 9.10 standalone.


Calling Business function DeleteAllRecords from Level 1 for DEMO. Application Name [], Version [] (BSFNLevel = 1)
**********************************************************************************
*** Start dumping data structure for business function DeleteAllRecords
**********************************************************************************
IN->[ 1] <Item>: szTableName <type>: STRING <Value>: [F0101]
IN->[ 2] <Item>: szErrorId <type>: STRING <Value>: []
**********************************************************************************
*** End dumping data structure for business function DeleteAllRecords
**********************************************************************************
Entering JDB_OpenTable(Table = F0101)
Exiting JDB_OpenTable(Table = F0101) (UserHandle 092D9B78) (hRequest 09214388) with Success
Entering JDB_DeleteTable (Table F0101) (hRequest 09214388)
TRUNCATE TABLE JDEData910.F0101
ANALYZE TABLE JDEData910.F0101 COMPUTE STATISTICS
Exiting JDB_DeleteTable with Success (Table F0101)
Entering JDB_FreeBhvr (UserHandle 092D9B78)
Exiting JDB_FreeBhvr with Success(UserHandle 092D9B78)
Entering JDB_CloseTable (hRequest 09214388)
Entering JDB_CloseTable(Table = F0101)
Exiting JDB_ClearBuffers with success.
Exiting JDB_CloseTable(Table = F0101) with Success
Exiting JDB_CloseTable with Success
**********************************************************************************
*** Start dumping data structure for business function DeleteAllRecords
**********************************************************************************
OUT->[ 1] <Item>: szTableName <type>: STRING <Value>: [F0101]
OUT->[ 2] <Item>: szErrorId <type>: STRING <Value>: []
**********************************************************************************
*** End dumping data structure for business function DeleteAllRecords
**********************************************************************************
Return value is 0 for DeleteAllRecords. (BSFNLevel = 1) <font color="red"> </font> <font color="red"> </font> <font color="red"> </font> TRUNCATE TABLE
 
Re: Truncate Table \'function\'?

JDB_DeleteTable is not really the same as truncating a table. JDB_DeleteTable w/o a key param is effectively doing a SQL "DELETE FROM TABLE". This will still create a transaction log on the DB server and check any constraints like referential integrity, etc. (which is not really applicable for a JDE database). Where as a "TRUNCATE TABLE" SQL will just drop all the records. No DB transaction log, no constraint checks, etc. The difference in processing time can be considerable. Additionally, depending on your DB provider, truncate table will most likely require elevated user permissions as opposed to delete table.

To do a truncate table or any other "straight sql" from JDE would most likely take some sort of DB stored procedure / DB table trigger which I think has been discussed on these message boards before.
 
Re: Truncate Table \'function\'?

Check the debug log that I included in my last post. It shows TRUNCATE TABLE rather than DELETE with no where clause.

You could give the function a try and see how quickly it returns.
 
Re: Truncate Table \'function\'?

Yup your right. I saw the truncate table statment in your post but since it didn't look like the actual SQL statements you normally see in the debug log (the one that is highlighted) I assumed the underlying SQL statement was still a DELETE TABLE. However, the actual SQL command in the debug log is in fact a trunate statement, not a delete just like you stated.

I did a quick test and here is from the debug log (that is assuming the sql statmenent shown is in fact what is actully executed):

TRUNCATE TABLE JDE_Development.DVDTA.F00095
 
Re: Truncate Table \'function\'?

It looks like B74B0250 is my new best friend!

Thanks, guys!

(db)
 
Re: Truncate Table \'function\'?

...

Looking back over the thread... did you really truncate F0101?

<snip>
TRUNCATE TABLE JDEData910.F0101
</snip>

grin.gif


(db)
 
Re: Truncate Table \'function\'?

Yep, in our production environment
crazy.gif


It was actually in standalone and I took a copy before truncating.
 
Re: Truncate Table \'function\'?

Gents, I use this baby


B8000007
followed by
B8000002



Call GetInputOutputEnvHandles (B8000007) to get the handle to the output environment required by this function
BSFN B8000002

I think it uses the JDB_DeleteTable API

I've been using it for years and it's only recently they added the need to get the env pointer

Hope that helps
 
Back
Top