SytelineMaster

Here is a useful query to check user section that cause blocking / locking.

SELECT
d1.session_id,
d3.[text],
d1.login_time,
d1.login_name,
d2.wait_time,
d2.blocking_session_id,
d2.cpu_time,
d1.memory_usage,
d2.total_elapsed_time,
d2.reads,d2.writes,
d2.logical_reads,
d2.sql_handle
FROM sys.dm_exec_sessions d1
JOIN sys.dm_exec_requests d2 ON d1.session_id=d2.session_id
CROSS APPLY sys.dm_exec_sql_text(d2.sql_handle) d3

In Syteline 9, there is one significant database schema change.  All major tables like item are replaced with a view and _mst table.  There are some context variables need to be set, before you can run query against to the views.  Otherwise query to the view will return no data.  And here is the variable declaration, with sample query after that.

DECLARE @Br NVARCHAR(8)

SET @Br = ‘SiteName’’

DECLARE @Context NVARCHAR(100)

SET @Context = @Br

DECLARE @BinVar VARBINARY(128)

SET @BinVar = CONVERT (VARBINARY(128), @Context)

set CONTEXT_INFO @BinVar

 

 

select co.slsman,co.co_num,co.type, co.order_date, coitem.co_line, coitem.item, coitem.qty_ordered_conv,coitem.price_conv,co.exch_rate

, ca.name, coitem.qty_ordered_conv * coitem.price_conv ‘Amount’, coitem.co_release

from coitem

join co on co.co_num = coitem.co_num

left join custaddr ca on ca.cust_num = co.cust_num and ca.cust_seq = co.cust_seq

where isnull(co.slsman,”) = ”

Update 2/10/2015

You may now just call this Sp.

DECLARE @Infobar InfobarType;
EXEC [dbo].[SetSiteSp] ‘CCSSL’, @Infobar OUTPUT

They are not stated in Syteline manual, but from my experience, I would suggest the following routine after upgrade a Syteline DB.

1) Regenerate _all table, by using “Update _All Tables” form.

2) Regenerate trigger, by using “Trigger management” form.

3) Regenerate Replication Triggers, if you are using multi-site replication.

4) Drop all the _tt and _tmp tables.  These would force system to rebuild those temp tables.  Please refer to Information on the different temporary tables in a SyteLine database for a script to drop all those temp table.

In the SyteLine database you will find Temporary tables that start with:

1. tt_*
2. tmp_*
3. tmp_snapshot_*
4. tmp_staging_*

There are others that end with:

1. _ii
2. _dd
3. _all.

The first six are various forms of permanent "temporary" tables, used for temporary processing of data.

The *_all tables are used to remove the requirement that another site must be connected in order to query its data. They contain a subset of columns from the other sites.

More detail:

• tt_ and tmp_ tables are shared temporary tables that allow for less blocking on the actual data tables while processing a limited number of records. Since the actual data table is queried when users are viewing a form, it is advantageous to move processing of smaller amounts of records to a temporary table thereby avoiding contention in the database. There is no real difference between those that start with tt_ and those that start with tmp_. tmp_ is the new standard prefix for tables where data is temporarily stored to support some process. The prefix tt_ was used for that functionality converted from SL6 where tt-* was used.

• tmp_snapshot and tmp_staging are generally used by Print/Post forms. tmp_snapshot_ is a static copy of the data so that printing uses the same data as posting (avoiding problems where someone changes the data between the two steps). tmp_staging is basically a RowPointer to the original record, plus some flags to indicate what steps of print/post have been completed.

• _ii & _dd tables were created to avoid SQL performance problems specific to trigger actions. They are used exclusively by triggers to pass data from the trigger to procedures that they call. Simplifying the trigger decreases the likelihood that SQL will need to recompile the trigger.

• Another important table of note is dynamic_table. This table dictates how permanent "temporary" tables are built. Since some of these tables are snapshots of other tables, database schema maintenance is less error prone when a change to the real table is automatically applied to the permanent "temporary" table. This also allows for more fluid introduction of UET’s into standard SyteLine processing.

• The life span of the data in all of these tables is intended to be short. Once all steps are complete in any given application, the records (which are segregated by by ProcessId) should programmatically be deleted. If records persist, then it is a sign of abnormal application shutdown, or a bug in the code which fails to execute the cleanup. If you know that no one is in a given application, for instance if you have shut down SQL services, you can delete the records in the table, but note that this should not be a routine action as these tables should be self-purging and may have entries in them relating to scheduled tasks that have not yet been activated.

• If the table is listed in dynamic_table, then if needed, the table can be deleted and the system will recreate it when necessary.

The following script will delete all temporary tables that are dynamic and will be recreated by the system when needed.
DO NOT MODIFY SCRIPT – – – -ALWAYS MAKE BACKUP OF DATABASE BEFORE RUNNING SCRIPT

declare @command1 nvarchar(2000)
declare @command2 nvarchar(2000)
declare @whereand nvarchar(2000)

set @command1 = ‘Drop table ?’
set @command2 = ‘Print "Dropped table ?"’
set @whereand = ‘and
(o.name like "tmp_ap%"
or o.name like "tmp_snapshot_ap%"
or o.name like "tmp_staging_ap%"
or o.name in (SELECT name from dynamic_table))
and o.name not in ("tmp_aps_sync","tmp_ap_quick_payments")’

exec sp_MSforeachtable
@command1 = @command1,
@command2 = @command2,
@whereand = @whereand

set @command1 = ‘Truncate table ?’
set @command2 = ‘Print "Truncated table ?"’
set @whereand = ‘and o.name in ("tmp_ap_quick_payments")’

exec sp_MSforeachtable
@command1 = @command1,
@command2 = @command2,
@whereand = @whereand

Below are some sample ways of constructing email body in Syteline event system.

BODY( SUBSTITUTE(“We have a new customer. Please see customer number {0} for customer: {1}.”, FP(“custNum”), FP(“custName”) ) )

This uses the common SUBSTITUTE function and use object property as parameter value.

BODY( IF( P(“POCost”) > 10000, GC(StdApprovalMessageBody), GC(StdRejectionMessageBody) ) )

Notice the IF function and combine with Global Constant

BODY( FILECONTENTS(“X:\SL\Event\Message\Body\myPreparedMessage.txt”) )

This actually inserts a whole text file.  But static text file is not really useful.

BODY( FILECONTENTS(“X:\SL\Event\Message\Body\” + IDO() + EVENTNAME() “.txt”) )

This one provide a bit more flexibility, allow you to use differ text file for differ event.

BODY( DBFUNCTION( “MsgBodyFunction”, EVENTNAME(), IDO(), P(“RowPointer”) ) )

Under the BackgroundColor property, use this expression

=IIF(RunningValue(Fields!grn_num.Value,countDistinct,Nothing) mod 2,"Silver","Transparent")

Table trigger is important part of Syteline data integrity.  There are many validation logics safe guarded by trigger program.  But in some circumstances, you may want to turn the trigger program off, in order load certain data.  This is specially true during data conversion of your Syteline implementation, you may need to use program to load data from outside data sources.

Below sample program will turn the trigger off, update jobmatl table, then turn the trigger back on again.

declare @SavedState LongListType

, @Infobar InfobarType

EXEC dbo.SetTriggerStateSp

@SkipReplicating  = 1

, @SkipBase         = 1

, @ScopeProcess     = 1

, @PreviousState    = @SavedState OUTPUT

, @Infobar          = @Infobar  OUTPUT

, @SkipAllReplicate = 1

, @SkipAllUpdate    = 1

UPDATE jobmatl

SET pick_date = @Today

FROM #JobmatlForPickList as jm

WHERE jobmatl.RowPointer = jm.RowPointer

exec dbo.RestoreTriggerStateSp

@ScopeProcess    = 1

, @SavedState      =  @SavedState

, @Infobar         = @Infobar OUTPUT

To extract the Event System metadata, please do the following:

1. On the Syteline utility server, go to <install drive>:\program files\Infor\Syteline
2. Double-click on AppMetadataTransport.exe
3. On the first few forms of the wizard, enter the appropriate information (e.g., select to export the metadata to an XML file, pick the appropriate configuration, browse to enter an export file name)
4. Next there will be several forms with different selection options starting with “IDOs to export”.  Skip these by clicking <Next> until you see the form for exporting Events.
5. On the Events form, select the option “Export Event MetaData for this AccessAs value” and select (null) as the AccessAs value.  Note:  Most user-defined event handlers are setup with AccessAs = Null.  If this is not the case in your environment, then you would specify your AccessAs value when extracting.
6. Then click through the rest until the last form.  Select <finish> on the last form.
7. The metadata will be extracted to the xml file specified in the wizard.  If Infor Support requested the Event metadata, please send them that xml file.

If you need to copy the metadata to another environment, then you would use the same utility to import the metadata into another database, but instead select to import and reference the XML file you just created.  IMPORTANT NOTE:  This will overwrite all event handlers with the same AccessAs value in the target database.  Please see linked KB 953806 for related CER:  CER 135998 – Need utility to import/export single events that will also not overwrite existing events when importing.

Additional information on copying events from one environment to another:

1)  If you have just a few event handlers that need copying and want to preserve the events in the target environment, then there are a couple of options:

• You can manually recreate the event handler records and then manually copy and paste the actions

or

• You can export events from both the source and target environments into separate xml files.  Make a copy of the target xml and edit the copy.  Manually copy and paste the source event data into the target xml copy.  If the event handler’s event name is the same between the two files, then assign a unique sequence number to the one(s) you are adding to the xml.  Then import the target copy which now contains both the original events and the few that have been added.

2)  If you have a lot of events and want to copy all of them, you could edit the xml file from the source events and change the Access As value and then import that xml file.  The target Access As value would be the new one you assign.  Please note that you would need to not only change the Access As value at the top of the XML document, but also add a node: <AccessAs>value</AccessAs> to every Event, Event Global Constant, Event Trigger, Event Initial State, and Event Handler in the document.  Once you import it, if you have to make changes to any of the events you imported under the different Access As value, you will have to change the Access As value in the database, or you would need to reset the AccessAs value on the events in the appropriate Event related tables to their original value.

Below are some sample ways of constructing email body in Syteline event system. 

BODY( SUBSTITUTE(“We have a new customer. Please see customer number {0} for customer: {1}.”, FP(“custNum”), FP(“custName”) ) )

BODY( IF( P(“POCost”) > 10000, GC(StdApprovalMessageBody), GC(StdRejectionMessageBody) ) )

BODY( FILECONTENTS(“X:\SL\Event\Message\Body\myPreparedMessage.txt”) )

BODY( FILECONTENTS(“X:\SL\Event\Message\Body\” + IDO() + EVENTNAME() “.txt”) )

BODY( DBFUNCTION( “MsgBodyFunction”, EVENTNAME(), IDO(), P(“RowPointer”) ) )