IT Tips

This is the select to list the area, extends quantity and last extend. select database() as 'Database', a."_AreaStatus-Areaname" as 'Area', a."_AreaStatus-Extents" as 'Extends', a."_AreaStatus-Lastextent" as 'Last extend' from pub."_AreaStatus" a

Use the SQL below to list the queries with more reads in your database. SELECT   distinct top 5 c."_Connect-IPAddress" as 'IP', c."_Connect-Type" as 'Connection type', c."_Connect-Name" as 'User', c."_Connect-Pid" as 'PID', f."_File-Name" as 'Table', u."_UserTableStat-Read" as 'Read', u."_UserTableStat-Create" as 'Write', u."_UserTableStat-Update" as 'Update', u."_UserTableStat-Delete" as 'Delete', database() as 'Database' FROM PUB."_UserTableStat" u, PUB."_File" f, PUB."_Connect" c where (u."_UserTableStat-Read" > 0 or u."_UserTableStat-Create" > 0 or u."_UserTableStat-Update" > 0 or u."_UserTableStat-Delete" > 0) and f."_File-Number" = u."_UserTableStat-Num" and c."_Connect-Id" = u....

With the select below you can see the size of MySQL databases.   SELECT         @@hostname as `Server`, t.table_schema AS "Database", SUM(t.data_length + t.index_length) / 1024 / 1024 AS "Size (MB)" FROM information_schema.TABLES t where t.table_schema not in ('information_Schema', 'test', 'mysql', 'performance_schema') GROUP BY table_schema ;

Use the select below to list your database, AI and BI size without go to operation system. select *   from   (SELECT database() as 'Database',sum(f."_FileList-Size")/1024 as 'Database size' FROM PUB."_Filelist" f where f."_FileList-Name" like '%.d%') d,   (SELECT sum(f."_FileList-Size")/1024 as 'AI' FROM PUB."_Filelist" f where f."_FileList-Name" like '%.a%') a,   (SELECT sum(f."_FileList-Size")/1024 as 'BI'   FROM PUB."_Filelist" f where f."_FileList-Name" like '%.b%') b

There are a lot of ways to monitor Oracle Database. With the select below you can list the SQL, time, and other informations of your slowly transactions. select v.SQL_TEXT,            v.PARSING_SCHEMA_NAME,            v.FIRST_LOAD_TIME,            v.DISK_READS,            v.ROWS_PROCESSED,            v.ELAPSED_TIME,            v.service       from v$sql v where   to_date(v.FIRST_LOAD_TIME,'YYYY-MM-DD hh24:mi:ss')>ADD_MONTHS(trunc(sysdate,'MM'),-2)   and PARSING_SCHEMA_NAME not in ('SYS', 'SYSTEM');

You can see the see the execution plan for your last SQL with the command below: select * from pub."_SQL_Qplan" 

In Openedge database you can see last programm or SQL execution from each connection. To enable this feature first you have to connect to a database with promon. Example: $DLC/bin/promon database After this use the sequence below:   R &D 1 18 2   1, 2 or 3 At this point you can choose between th options: Single — Only the current ABL program and line number, or a single SQL statement is reported by the ABL client. Stack — The call stack up to the current ABL program and line number. One Time Request — The ABL or SQL client report tracing information once. Once reported, database-request statement caching is turned off.   After this you can use the SQLs below the get database informations: List all connections with cache information: select database() as 'Banco', c."_Connect-type" , c."_Connect-PID" as 'PID', c."_connect-ipaddress" as 'IP', c."_Connect-...

You can see Openedge database, AI and BI size with SQL below: select * from (SELECT database() as 'Database', sum(f."_FileList-Size")/1024 as 'Base'  FROM PUB."_Filelist" f where f."_FileList-Name" like '%.d%') d, (SELECT sum(f."_FileList-Size")/1024 as 'AI' FROM PUB."_Filelist" f where f."_FileList-Name" like '%.a%') a, (SELECT sum(f."_FileList-Size")/1024 as 'BI' FROM PUB."_Filelist" f where f."_FileList-Name" like '%.b%') b

In OpenEdge database you can use 4GL and SQL commands. I like SQL commands because I don't know to much about 4GL :D. Below, there is a SQL script to show blocked transactions and locks. select         distinct database() as 'Database', c."_Connect-Id" as 'ID', c."_Connect-Time" as 'Date', t."_Trans-txtime" as 'Start time', t."_Trans-Duration" as 'Time in seconds', c."_Connect-Name" as 'User', c."_Connect-IPAddress" as 'IP', c."_Connect-Pid" as 'PID SO', t.TBL as 'Table', l."_Lock-Flags" as 'Type' from         pub."_trans" t, pub."_connect" c, PUB."_Lock" l, sysprogress.SYSTABLES t where         t."_Trans-State" = 'ACTIVE' and t."_Trans-Usrnum" = c."_Connect-Usr" and c."_Connect-Usr" = l."_Lock-Usr...

Sometimes we have to start an IIS and Jboss, Wildfly, Tomcat, etc in the same serer, but by default, IIS listen all IP adress. You can list the IP address used by IIS with  this command: netsh http show iplisten To change the listen you can use a command like this: netsh http add iplisten ipaddress="IP"

You can identify Oracle version using the select and the image below: SELECT * FROM PRODUCT_COMPONENT_VERSION;

How to list memory utilization by database? You can do that with this script: DECLARE @total_buffer INT ; SELECT @total_buffer = cntr_value    FROM sys.dm_os_performance_counters    WHERE RTRIM([object_name]) LIKE '%Buffer Manager'    AND counter_name = 'Total Pages' ; ; WITH src AS (    SELECT        database_id, db_buffer_pages = COUNT_BIG(*)        FROM sys.dm_os_buffer_descriptors        --WHERE database_id BETWEEN 5 AND 32766        GROUP BY database_id ) SELECT    [db_name] = CASE [database_id] WHEN 32767        THEN 'Resource DB'        ELSE DB_NAME([database_id]) END ,    db_buffer_pages,    db_buffer_MB = db_buffer_pages / 128,    db_buffer_percent = CONVERT ( DECI...

Sometimes we need to send an e-mail when our SQL Server identify some blocking process. The script below will help you. Just create a job, change the information of your mail profile, recipients, subject and body. SET NOCOUNT ON ; DECLARE @ blockingProcess INT SELECT   @ blockingProcess = COUNT (*) FROM     sys.dm_tran_locks L         JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id         JOIN sys.objects O ON O.object_id = P.object_id         JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id         JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id         JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT .transaction_id         JOIN ...