Using SQL Command Editor to call BW DSO, Possible?

[former_member205400]

SAP ORA Experts,

I have a BW DSO and I'm wanting to run some SQL Queries to get some stats on the data details.

That being said I'm using the SQL Command Editor in DB02 -> Performance -> Add'l Functions.

I tried some simple queries like:

select * from "SAPSR3./BIC/AZCO_O00900"

and

I'm getting some error messages that the table does not exist, but it does exist just not how I'm trying to call it.

Do I have to like specify the tablespace that I want to use or something?

Mike

[lbreddemann · Accepted solution]

Nope, you're doing everything correctly.

The SQL Command editor in the DBACockpit is designed so that only database instance relevant tables can be accessed, but NO business data.

If you think about it, you wouldn't want to have such an easy way build-in to bypass any authorization check.

Use transaction SE16 to check table contents or alternatively, if you have got permission to run SE38/SA38/SE37, you may use report RSDU_EXEC_SQL to run your custom SQL statements.

regards,

Lars

[former_member205400 · Accepted solution]

Lars,

Nice!!! that's what I was thinking ... and it makes sence, right.

SO how about this:

I do have access to SE37 so I'm on RSDU_EXEC_SQL but I'm wondering about the syntax there.

If I go to DB02 -> Perf -> SQL Statement Anlysis -> Table Access I can see tables accessed and SQL Statements.

If can't just put those statements in RSDU_EXEC_SQL can I?

What kind of format do i need.

Mike

[lbreddemann · Accepted solution]

>

> Lars, 
> 
> Nice!!! that's what I was thinking ... and it makes sence, right.
> 
> SO how about this:
> 
> I do have access to SE37 so I'm on RSDU_EXEC_SQL but I'm wondering about the syntax there.
> 
> If I go to DB02 -> Perf -> SQL Statement Anlysis -> Table Access I can see tables accessed and SQL Statements.
> 
> If can't just put those statements in RSDU_EXEC_SQL can I?
> 
> What kind of format do i need.
> 
> Mike

RSDU_EXEC_SQL provides an option to display the EXPLAIN PLAN.

Use this to check the Statement before execution.

BTW: for auditing options you can also setup logging of statements run by this transaction!

regards,

Lars

[former_member205400 · Accepted solution]

Lars,

SE38 .... Very helpful!! Thanks!!

One more question to get me rolling on a couple queries if you know

SELECT
CALDAY,
FROM "/BIC/AZCO_O00800"

this works ....... produces a list of all my calendar days in the table

SELECT
CALDAY,
LPAD("DT"."CALDAY",000004, 0 )
FROM "/BIC/AZCO_O00800"

doesn't work .... trying to change the format of the date field to display the first 4 characters ???

SELECT
CALDAY,
count(*)
FROM "/BIC/AZCO_O00800"

doesn't work .... trying to display the number of ocurrences of that calday

Thanks again,

Mike

[former_member205400 · Accepted solution]

Lars,

WooooHooo ... these all work

SELECT *
FROM "/BIC/AZCO_O00800"

SELECT 
CALDAY 
FROM "/BIC/AZCO_O00800"

SELECT
CALDAY,
Count(*) as "my_count"
FROM "/BIC/AZCO_O00800"
GROUP BY CALDAY

SELECT
lpad(CALDAY,4,0) AS "my_year",
COUNT( * )  AS "my_count"
FROM "/BIC/AZCO_O00800"
GROUP BY lpad(CALDAY,4,0) 

TY Very Much, Mike