topic Re: Read data from DB02/DBACOCKPIT. in Application Development and Automation Discussions

Read data from DB02/DBACOCKPIT.

former_member565266 — Tue, 13 Oct 2020 10:54:01 GMT

Hello Experts,

we will need to collect some data from DB02/DBACOCKPIT. We will need to build a custom tool to fetch and keep these data in custom tables.
Please help to get data from DB02/DBACOCKPIT. Is there any FM available to read data.

Regards,

Satish Kumar.

Re: Read data from DB02/DBACOCKPIT.

Private_Member_467521 — Tue, 13 Oct 2020 11:47:18 GMT

Hi satish.kumar144,

You will have to work with the tools of the specific database you have in your landscape, mostly through native SQL statements.

In the case of Oracle database for instance, dba_data_files and dba_free_space are not tables in the ABAP dictionary, they are dictionary views in the underlying Oracle database.

DB02 gives information on allocation of data in the Oracle database.

Considering primary functions of DB02 in the Database are:

Space analysis of the SAP database and transaction logs showing space allocated, free space and used space.
List of basic options and the recovery model that have been set for the database. By default, automatic creation and update of statistics is set for an SAP system.
List of all files of the SAP database and the free space available on disk.
Specifies the space allocated for individual files, the amount of space used and other data.

Here as some SQL statement examples you can use:

Dba_data_files:

The dba_data_files means total size of the data file. The data file size is total number of the dba_free_space + dba_segments.

SQL> select tablespace_name,sum(bytes)/(1024*1024*1024) from dba_data_files group by tablespace_name order by 1;

This example display the total size of the data file:

SQL> select sum(bytes)/(1024*1024*1024) from dba_data_files;SUM(BYTES)/(1024*1024*1024)

Dba_segments:

The dba_segments means used size of the data file.

SQL> select tablespace_name,sum(bytes)/(1024*1024*1024) from dba_segments group by tablespace_name order by 1;

This example display the total size of the segments:

SQL> select sum(bytes)/(1024*1024*1024) from dba_segments;SUM(BYTES)/(1024*1024*1024)

Dba_free_space:

The dba_free_space means free size of the data file.

SQL> select tablespace_name,sum(bytes)/(1024*1024*1024)from dba_free_space group by tablespace_name order by 1;

This example display the total size of the free space:

SQL> select sum(bytes)/(1024*1024*1024) from dba_free_space;SUM(BYTES)/(1024*1024*1024)

These tables are accessed using the native SQL statements .

Example IN DB02

dba_data_files

EXEC SQL PERFORMING APP_1.SELECT TABLESPACE_NAME, SUM(NVL(BYTES,0))/1024FROM DBA_DATA_FILESGROUP BY TABLESPACE_NAME INTO :TAB_1ENDEXEC.

dba_free_space

EXEC SQL PERFORMING APP_2.SELECT TABLESPACE_NAME, SUM(NVL(BYTES,0))/1024 FROM DBA_FREE_SPACEGROUP BY TABLESPACE_NAME INTO :TAB_2ENDEXEC.

dba_tablespaces

EXEC SQL PERFORMING DBA_SEG_ANALYZER2.SELECT * FROM DBA_TABLESPACESINTO :TAB_3ENDEXEC.

dba_segments

EXEC SQL PERFORMING DBA_SEG_ANALYZER2.SELECT OWNER, SEGMENT_NAME, TABLESPACE_NAME,EXTENTS, SEGMENT_TYPE, BYTES / 1024FROM DBA_SEGMENTSINTO :DBA_SEG_ANENDEXEC.

Hope it helps

Cheers,

Luis 🙂

Re: Read data from DB02/DBACOCKPIT.

former_member565266 — Tue, 13 Oct 2020 13:52:42 GMT

Thank you Luis for quick reply.

we are using DB2 database and need to fetch below data and keep those data into custom table.

Total size, used size, free size, Tbsp NUM indexs, Tbsp NUM tables. Please find the attached screenshot. db02.png

Please help me with the logic/ standard FM.

Thanks,

Satish Kumar.

Re: Read data from DB02/DBACOCKPIT.

Private_Member_467521 — Tue, 13 Oct 2020 15:25:05 GMT

HI satish.kumar144,

If you DB is Oracle, use the following DB02_ORA function modules list.

db02-ora.jpg

Cheers,

Luis 🙂

Re: Read data from DB02/DBACOCKPIT.

former_member565266 — Wed, 14 Oct 2020 08:13:49 GMT

Hi Luis,

Thank you for reply, we are using DB2 as Database. Please help me to get those data from DBACOCKPIT/DB02. This is very urgent.

Attached file is output fields, Path is DB02-->SPACE-->DATABASE .db02.png

Regards,

Satish Kumar

Re: Read data from DB02/DBACOCKPIT.

Private_Member_467521 — Wed, 14 Oct 2020 12:54:05 GMT

HI satish.kumar144,

I personally did not have experience into DB2 using the function modules. You would need to investigate further. Here is what I have found - it can help you as a starting point:

db2-kpis.jpg

Our purpose here is to help the community to the best of our knowledge, but if you have something urgent that is impacting your ability to deliver, I strongly suggest you create a ticket with SAP and do not rely only on this channel to get your problem addressed. Also, it is your responsibility to accept and use any suggestion or recommendation shared here.

Cheers,

Luis 🙂

Re: Read data from DB02/DBACOCKPIT.

former_member565266 — Thu, 15 Oct 2020 02:20:22 GMT

Thank you so much Luis for helping/advise to my query. I will raise a ticket to SAP for the same and will have a look at FM which you have provided.

Good day.

Regards,

Satish Kumar