How to stop running endless DS batch job?

1) From inside Designer:

Go to the Monitor tab in Project Area window, right click your running batch job (with status green) and ‘Kill Job’.

If not able to do that, you can get this done in Data Services Management Console.

2) Login to DS Management Console. Go to Administrator > Batch

Choose your repository, select the running job and click ‘Abort’ at the bottom.

Advertisements

How to find the slow running objects in DI?

SQL Server query:

SELECT AL_LANG.NAME,
AL_HISTORY.START_TIME,
AL_HISTORY.END_TIME,
AL_HISTORY.EXECUTION_TIME,
ObjName.VALUE as Object_Name,
ObjType.VALUE as Object_Type,
cast(ExecTime.VALUE as numeric(10,0)) as Object_Execution_Time
FROM AL_LANG
JOIN AL_HISTORY
ON (AL_HISTORY.INST_EXEC_KEY = AL_LANG.OBJECT_KEY)
JOIN AL_STATISTICS ExecTime
ON (ExecTime.OBJECT_KEY = AL_HISTORY.OBJECT_KEY AND
ExecTime.NAME = ‘EXECUTION_TIME’)
JOIN AL_STATISTICS ObjType
ON (ObjType.OBJECT_KEY = ExecTime.OBJECT_KEY AND
ObjType.KEY2 = ExecTime.KEY2 AND
ObjType.NAME = ‘OBJECT_TYPE’ AND
ObjType.VALUE IN (‘Workflow’, ‘Dataflow’))
JOIN AL_STATISTICS ObjName
ON (ObjName.OBJECT_KEY = ExecTime.OBJECT_KEY AND
ObjName.KEY2 = ExecTime.KEY2 AND
ObjName.NAME = ‘OBJECT_NAME’)
WHERE AL_LANG.NAME = ‘<enter job name here>’
AND AL_LANG.OBJECT_TYPE = 0 — To help use the index and narrow the scope
AND AL_HISTORY.OBJECT_KEY = (SELECT MAX(SUBQ.OBJECT_KEY)
FROM AL_HISTORY SUBQ
WHERE SUBQ.INST_EXEC_KEY = AL_LANG.OBJECT_KEY
and has_error = 0 and status = ‘D’ AND END_TIME IS NOT NULL)
AND AL_LANG.VERSION = (select max(subq.version)
from AL_LANG subq
where subq.OBJECT_TYPE = AL_LANG.OBJECT_TYPE
and subq.GUID = AL_LANG.GUID)
ORDER BY cast(ExecTime.VALUE as numeric(10,0)) DESC;

How to find if the specific column existed in specified table SQL SERVER 2008?

I have to find all the table names which is having a specific column and to know the no of records available in those tables.

Here is the query.

When executing the query should remove the UNION at the end of the query.

SELECT ‘SELECT ‘ + ””+ t.name +””+’,’+ ‘ MAX(<column_name>) FROM ‘+ t.name + ‘ UNION ‘ AS TableName
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE T.name LIKE ‘<table_search_text>%’ and c.name ='<column_name>’

 

LCM BIAR file limitation

Funny note from SAP about BOBJ BI4.0 LCM BIAR :-
Best Practice: SAP Business Objects recommends that you select a small number of infoobjects,
which should not exceed 100 at a time, for promotion to obtain optimum Performance of the Promotion Management Tool.
(page: 30 of User Guide: xi4_lcm_user_en.pdf )

How to take entire BOE backup then! Old Importwizard is much more better.

Finding a column used by the tables in SQL Server database

How to find a column, has been used by the tables in SQL Server database?

SELECT
TABLE_CATALOG
,TABLE_SCHEMA
,TABLE_NAME
,COLUMN_NAME
,DATA_TYPE
,CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = ”INT” AND COLUMN_NAME LIKE ”%<COLUMN_NAME>%”

SELECT T.NAME AS TABLE_NAME,
SCHEMA_NAME(SCHEMA_ID) AS SCHEMA_NAME,
C.NAME AS COLUMN_NAME
FROM SYS.TABLES AS T
INNER JOIN SYS.COLUMNS C ON T.OBJECT_ID = C.OBJECT_ID
WHERE C.NAME LIKE ‘%<COLUMN_NAME>%’
ORDER BY SCHEMA_NAME, TABLE_NAME;

How to track the data of N no of tables manually

How to track the data of N no of tables manually?

If we want to know how data has been loading into DWH tables, then we can use the below query:

A query to track the data of tables:

select ‘TABLE1’ Table_Name,count(1) as Row_Count from TABLE1 UNION

select ‘TABLE2’ Table_Name,count(1) from TABLE2 UNION

select ‘TABLE3’ Table_Name,count(1) from TABLE3 UNION

select ‘TABLE4’ Table_Name,count(1) from TABLE4 UNION

select ‘TABLE5’ Table_Name,count(1) from TABLE5 UNION

select ‘TABLE6’ Table_Name,count(1) from TABLE6

TABLENAME and INDEXNAME from the database

Find TABLENAME and INDEXNAME for fragmented indexes

Create a function:

CREATE FUNCTION dbo.INDEX_NAME (@object_id int, @index_id int)
RETURNS sysname
AS
BEGIN
RETURN(SELECT name FROM sys.indexes WHERE object_id = @object_id and index_id = @index_id)
END;
GO

Run the below query:

SELECT
OBJECT_NAME(object_id) AS TABLENAME
,dbo.INDEX_NAME(object_id, index_id) AS INDEXNAME
,avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL)
WHERE avg_fragmentation_in_percent > 20
AND index_type_desc IN(‘CLUSTERED INDEX’, ‘NONCLUSTERED INDEX’)