STEPS TO REPRODUCE
- Enable the Enterprise Manager Statistics Trace. It will provide you with the Intelligence Server queries for collecting and logging statistics. The trace can be found in the Diagnostics and Configuration Tool, selecting the CastorServer Instance, Kernel > EM Migration Trace. See the details in KB19579
- Start a session with Command Manager or any other client application which is installed in the Intelligence Server machine.
The session entries from the Statistics Trace will look like the following:
2015-09-08 16:11:18.934+01:00 [HOST:POL-BAVALOS][PID:5276][THR:9604][Kernel][Statistics Trace] TRACE:MsiStatisticsMgr::ExecuteSql(iSql=insert into IS_SESSION_STATS (SESSIONID,SERVERID,SERVERNAME,SERVERINSTNAME,SERVERMACHINE,WEBMACHINE,USERID,CLIENTMACHINE,CONNECTTIME,DISCONNECTTIME,EVENTSOURCE,EVENTNOTES,PROJECTID,SESSIONEVENT,EVENTTIME,DAY_ID,HOUR_ID,MINUTE_ID,RECORDTIME) values ('D0A3C48E45CC0743E79CB8B80848C7D7', 'A7E705DB4ACD5C88647AD5B8135C4EB0', 'Strategy Analytics Modules Server', null, 'POL-BAVALOS:34952', null, '54F3D26011D2896560009A8E67019608', 'Server Machine: 10.80.10.185 Client Machine: ', {ts'2015-09-08 16:11:09'}, {ts'1900-01-01 01:00:00'}, 13, null, 'NA', -1, {ts'1900-01-01 01:00:00'}, {ts'2015-09-08 16:11:09'}, 2015090816, 2.01509081611E+011,GETDATE()))Notice that IS_SESSION_STATS CLIENTMACHINE is something similar to: "Server Machine: 10.80.10.185 Client Machine: ", (Client Machine is blank).
The same happens when the session is created by other client applications like Integrity Manager, Configuration Wizard, etc. which are installed in the Intelligence Server Machine.
- During the data load, the CASE statement for Transaction 3 Sequence 1, results in a NULL value being inserted which produces an error and the failed data load.
MAX(CASE
WHEN EVENTSOURCE = 7 THEN UPPER(ST.SERVERMACHINE)
WHEN ST.CLIENTMACHINE IS NULL THEN ''N/A''
WHEN SUBSTRING(UPPER(ST.CLIENTMACHINE),1,15) = ''SERVER MACHINE:''
THEN UPPER(SUBSTRING(ST.CLIENTMACHINE,17,CHARINDEX(''CLIENT MACHINE:'',UPPER(ST.CLIENTMACHINE)) + 16))
ELSE UPPER(ST.CLIENTMACHINE) END)
Error executing SQL :Execute Query failed.
Error type: Odbc error. Odbc operation attempted: SQLExecDirect. [23000:1400: on SQLHANDLE] [Strategy][ODBC Oracle Wire Protocol driver][Oracle]ORA-01400: cannot insert NULL into("SCHEMA"."IS_SESSION"."EM_CLIENT_MACHINE")INSERT INTO IS_SESSION (EM_RECORD_TS, EM_LOAD_TS, IS_SESSION_ID, IS_SERVER_ID, EM_APP_SRV_MACHINE, EM_WEB_SRV_MACHINE, EM_USER_ID, EM_CLIENT_MACHINE, IS_CONNECT_TS, IS_DISCONNECT_TS, IS_TMP_DISCON_TS, IS_CONNEC_DH_ID, IS_DISCON_DH_ID, IS_SESSION_TM_SEC, EM_CONNECT_SOURCE, EM_DISCONN_REASON, DAY_ID, HOUR_ID, MINUTE_ID) SELECT EM_RECORD_TS, EM_LOAD_TS, IS_SESSION_ID, IS_SERVER_ID, EM_APP_SRV_MACHINE, EM_WEB_SRV_MACHINE, EM_USER_ID, EM_CLIENT_MACHINE, IS_CONNECT_TS, IS_DISCONNECT_TS, IS_TMP_DISCON_TS, IS_CONNEC_DH_ID, IS_DISCON_DH_ID, (CAST(IS_TMP_DISCON_TS AS DATE) - CAST(IS_CONNECT_TS AS DATE)) * 86400, EM_CONNECT_SOURCE, EM_DISCONN_REASON, DAY_ID, HOUR_ID, MINUTE_ID FROM IS_SESSION_TMP1 WORKAROUNDYou can manually update the CLIENTMACHINE fields showing values like: "Server Machine: 10.80.10.185 Client Machine: " to something like: "Server Machine: 10.80.10.185 Client Machine: 1.1.1.1". Simply enter an IP or a dummy value for Client Machine.
This will resolve the short-term issue which is the data load execution. However, empty Client Machine fields will be still recorded each time a session is created from any of the client applications.
Execute the following query against the Enterprise Manager Statistics:
SELECT CLIENTMACHINE, COUNT(*) FROM IS_SESSION_STATS
GROUP BY CLIENTMACHINE
ORDER BY CLIENTMACHINE
In this case, there were eight records containing a problematic Client Machine value:
R01_Y1999_SALES R01_Y2000_SALES R02_Y1999_SALES R02_Y2000_SALES YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200MONTH_IDSTORE_IDSALES_AMT1999010001501999010002200
CLIENTMACHINE
COUNT(*)
Server Machine: 23.4.3.146 Client Machine:
8
CLIENTMACHINEEVENTSOURCECOUNT(*)Server Machine: 11.30.17.243 Client Machine:264Server Machine: 11.30.17.243 Client Machine:132
|
R01_Y1999_SALES R01_Y2000_SALES R02_Y1999_SALES R02_Y2000_SALES YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200MONTH_IDSTORE_IDSALES_AMT1999010001501999010002200
CLIENTMACHINE
COUNT(*)
Server Machine: 23.4.3.146 Client Machine:
8
CLIENTMACHINEEVENTSOURCECOUNT(*)Server Machine: 11.30.17.243 Client Machine:264Server Machine: 11.30.17.243 Client Machine:132
|
R01_Y1999_SALES R01_Y2000_SALES R02_Y1999_SALES R02_Y2000_SALES YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200MONTH_IDSTORE_IDSALES_AMT1999010001501999010002200
CLIENTMACHINE
COUNT(*)
Server Machine: 23.4.3.146 Client Machine:
8
CLIENTMACHINEEVENTSOURCECOUNT(*)Server Machine: 11.30.17.243 Client Machine:264Server Machine: 11.30.17.243 Client Machine:132
|
R01_Y1999_SALES R01_Y2000_SALES R02_Y1999_SALES R02_Y2000_SALES YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200MONTH_IDSTORE_IDSALES_AMT1999010001501999010002200
CLIENTMACHINE
COUNT(*)
Server Machine: 23.4.3.146 Client Machine:
8
CLIENTMACHINEEVENTSOURCECOUNT(*)Server Machine: 11.30.17.243 Client Machine:264Server Machine: 11.30.17.243 Client Machine:132
|
- Make sure you back up the database before doing any changes to it.
- Manually update the affected records with a dummy value:
UPDATE IS_SESSION_STATS
SET CLIENTMACHINE = ' clientApp'
WHERE CLIENTMACHINE = 'Server Machine: 23.4.3.146 Client Machine: '
- Once you make sure that IS_SESSION_STATS table does not contain problematic records, check the status of previous data load from the table EM_IS_LAST_UPDATE. Only the first row should have the column IS_STATUS = 0.
SELECT * FROM EM_IS_LAST_UPDATE
WHERE IS_STATUS= 0
AND EMLU_ID > 1;
If the above statement returns more than a row, manually update to “1” all the rows except the first one.
- Execute also the following SQL statement:
DELETE FROM EM_RECOVERY;
- Execute the data load manually.
TO KNOW MORE:
CLIENTMACHINE is the name of the client machine or its IP address if the name is not available.
The CLIENTMACHINE data is processed in the following manner:
- The EM_CLIENT_MACHINE lookup table gets its values from the EM_CLIENT_MACHINE column of the IS_SESSION table. Transaction 7, Sequence 2 of the ETL process. You can check this on the MSTRMigration log.
- IS_SESSION takes the values from IS_SESSION_TMP1, which is a temporary table where the CASE statement produces the issue.
- The CASE statement gets the data from IS_SESSION_ST_VW, which is a view of the IS_SESSION_STATS table.
The IS_SESSION_STATS table is the origin of the CLIENTMACHINE values. These, are directly recorded into this table by the Intelligence Server. Therefore, it is only in this table where we should apply any changes when this issue appears.
Using the EVENTSOURCE column, and based on KB35327: Is it possible to track the client machine used to connect to MicroStrategy Web? , we can identify the origin of the problematic sessions:
SELECT CLIENTMACHINE, EVENTSOURCE, count(*) FROM IS_SESSION_STATS
GROUP BY CLIENTMACHINE, EVENTSOURCE
ORDER BY CLIENTMACHINE
WHERE CLIENTMACHINE = ‘Server Machine: 11.30.17.243 Client Machine:’
R01_Y1999_SALES R01_Y2000_SALES R02_Y1999_SALES R02_Y2000_SALES YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200MONTH_IDSTORE_IDSALES_AMT1999010001501999010002200
CLIENTMACHINE
COUNT(*)
Server Machine: 23.4.3.146 Client Machine:
8
CLIENTMACHINEEVENTSOURCECOUNT(*)Server Machine: 11.30.17.243 Client Machine:264Server Machine: 11.30.17.243 Client Machine:132
|
R01_Y1999_SALES R01_Y2000_SALES R02_Y1999_SALES R02_Y2000_SALES YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200MONTH_IDSTORE_IDSALES_AMT1999010001501999010002200
CLIENTMACHINE
COUNT(*)
Server Machine: 23.4.3.146 Client Machine:
8
CLIENTMACHINEEVENTSOURCECOUNT(*)Server Machine: 11.30.17.243 Client Machine:264Server Machine: 11.30.17.243 Client Machine:132
|
R01_Y1999_SALES R01_Y2000_SALES R02_Y1999_SALES R02_Y2000_SALES YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200MONTH_IDSTORE_IDSALES_AMT1999010001501999010002200
CLIENTMACHINE
COUNT(*)
Server Machine: 23.4.3.146 Client Machine:
8
CLIENTMACHINEEVENTSOURCECOUNT(*)Server Machine: 11.30.17.243 Client Machine:264Server Machine: 11.30.17.243 Client Machine:132
|
R01_Y1999_SALES R01_Y2000_SALES R02_Y1999_SALES R02_Y2000_SALES YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200MONTH_IDSTORE_IDSALES_AMT1999010001501999010002200
CLIENTMACHINE
COUNT(*)
Server Machine: 23.4.3.146 Client Machine:
8
CLIENTMACHINEEVENTSOURCECOUNT(*)Server Machine: 11.30.17.243 Client Machine:264Server Machine: 11.30.17.243 Client Machine:132
|
R01_Y1999_SALES R01_Y2000_SALES R02_Y1999_SALES R02_Y2000_SALES YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200MONTH_IDSTORE_IDSALES_AMT1999010001501999010002200
CLIENTMACHINE
COUNT(*)
Server Machine: 23.4.3.146 Client Machine:
8
CLIENTMACHINEEVENTSOURCECOUNT(*)Server Machine: 11.30.17.243 Client Machine:264Server Machine: 11.30.17.243 Client Machine:132
|
R01_Y1999_SALES R01_Y2000_SALES R02_Y1999_SALES R02_Y2000_SALES YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200MONTH_IDSTORE_IDSALES_AMT1999010001501999010002200
CLIENTMACHINE
COUNT(*)
Server Machine: 23.4.3.146 Client Machine:
8
CLIENTMACHINEEVENTSOURCECOUNT(*)Server Machine: 11.30.17.243 Client Machine:264Server Machine: 11.30.17.243 Client Machine:132
|
R01_Y1999_SALES R01_Y2000_SALES R02_Y1999_SALES R02_Y2000_SALES YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200MONTH_IDSTORE_IDSALES_AMT1999010001501999010002200
CLIENTMACHINE
COUNT(*)
Server Machine: 23.4.3.146 Client Machine:
8
CLIENTMACHINEEVENTSOURCECOUNT(*)Server Machine: 11.30.17.243 Client Machine:264Server Machine: 11.30.17.243 Client Machine:132
|
R01_Y1999_SALES R01_Y2000_SALES R02_Y1999_SALES R02_Y2000_SALES YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200MONTH_IDSTORE_IDSALES_AMT1999010001501999010002200
CLIENTMACHINE
COUNT(*)
Server Machine: 23.4.3.146 Client Machine:
8
CLIENTMACHINEEVENTSOURCECOUNT(*)Server Machine: 11.30.17.243 Client Machine:264Server Machine: 11.30.17.243 Client Machine:132
|
R01_Y1999_SALES R01_Y2000_SALES R02_Y1999_SALES R02_Y2000_SALES YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200MONTH_IDSTORE_IDSALES_AMT1999010001501999010002200
CLIENTMACHINE
COUNT(*)
Server Machine: 23.4.3.146 Client Machine:
8
CLIENTMACHINEEVENTSOURCECOUNT(*)Server Machine: 11.30.17.243 Client Machine:264Server Machine: 11.30.17.243 Client Machine:132
|
- Command Manager, EVENTSOURCE = 13
- Integrity manager, EVENTSOURCE = 26
Reference number: KB439408
