Category Archives: Databases

Creating ODBC system DNS with command line

%WINDIR%\System32\odbcconf.exe CONFIGSYSDSN "SQL Server" "DSN=macoprod|Description=macoprod|SERVER=10.2.151.18|Trusted_Connection=No|Database=macoprod"
%WINDIR%\SysWOW64\odbcconf.exe CONFIGSYSDSN "SQL Server" "DSN=macoprod|Description=macoprod|SERVER=10.2.151.18|Trusted_Connection=No|Database=macoprod"

Example of an Indexed View in SQL Server

CREATE VIEW C_FINANCEENTRYPV WITH SCHEMABINDING AS
  SELECT
     ACCOUNTNUMBER
    ,ENTRYDATE
    ,Sum(IsNull(FINANCEENTRY.DEBITBASE,0))  DEBITBASE
    ,Sum(IsNull(FINANCEENTRY.CREDITBASE,0)) CREDITBASE
    ,Count_Big(*) CNT
  FROM
    dbo.FINANCEENTRY FINANCEENTRY
GROUP BY 
    ACCOUNTNUMBER,
    ENTRYDATE
GO

CREATE UNIQUE CLUSTERED INDEX C_FINANCEENTRYPV_01 ON C_FINANCEENTRYPV (
  ACCOUNTNUMBER,
  ENTRYDATE
)
GO

SELECT * FROM C_FINANCEENTRYPV WITH (NOEXPAND)

How to Allow Remote TCP/IP Connections for SQL Server

SQL Server Configuration Manager > SQL Server Network Configuration > Protocols for SQLEXPRESS > TCP/IP
TCP/IP Enabled
IPAll > TCP Dynamic Ports is blank
IPAll > TCP Port = 1433
SQL Server Management Studio > Server Properties > Security
Server authentication = SQL Server and Windows Authentication mode
Services > SQL Server Browser
Enable and start
Windows Firewall with Advanced Security > Inbound Rules > New Rule… > Port
Allow the connection for the port 1433

Misc. Dates – SQL Server

SELECT

  DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, 0)                    AS FIRST_DAY_PREVIOUS_YEAR,
  DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)                        AS FIRST_DAY_CURRENT_YEAR,
  DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, 0)                    AS FIRST_DAY_NEXT_YEAR,

  DATEADD(YEAR, DATEDIFF(YEAR, -1, GETDATE()) - 1, -1)                  AS LAST_DAY_PREVIOUS_YEAR,
  DATEADD(YEAR, DATEDIFF(YEAR, -1, GETDATE()), -1)                      AS LAST_DAY_CURRENT_YEAR,
  DATEADD(YEAR, DATEDIFF(YEAR, -1, GETDATE()) + 1, -1)                  AS LAST_DAY_NEXT_YEAR,

  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0)                  AS FIRST_DAY_PREVIOUS_MONTH,
  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)                      AS FIRST_DAY_CURRENT_MONTH,
  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)                  AS FIRST_DAY_NEXT_MONTH,
  
  DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()) - 1, -1)                AS LAST_DAY_PREVIOUS_MONTH,
  DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()), -1)                    AS LAST_DAY_CURRENT_MONTH,
  DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()) + 1, -1)                AS LAST_DAY_NEXT_MONTH,

  DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 1, 0)                      AS YESTERDAY,
  DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)                          AS TODAY,
  DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) + 1, 0)                      AS TOMORROW,

  DATEADD(WEEK, DATEDIFF(WEEK,0,GETDATE() - 7), 0)                      AS PREVIOUS_MONDAY,
  DATEADD(WEEK, DATEDIFF(WEEK,0,GETDATE()), 0)                          AS CURRENT_MONDAY,
  DATEADD(WEEK, DATEDIFF(WEEK,0,GETDATE() + 7), 0)                      AS NEXT_MONDAY,
  
  DATEADD(WEEK, DATEDIFF(WEEK,0,GETDATE()), 0) - 1                      AS PREVIOUS_SUNDAY,
  DATEADD(WEEK, DATEDIFF(WEEK,0,GETDATE() + 7), 0) - 1                  AS CURRENT_SUNDAY,
  DATEADD(WEEK, DATEDIFF(WEEK,0,GETDATE() + 14), 0) - 1                 AS NEXT_SUNDAY,

  DATEADD(YEAR, 1, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))        AS THE_DAY_PLUS_1YEAR,
  DATEADD(YEAR, -1, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))       AS THE_DAY_MINUS_1YEAR,
  
  DATEADD(MONTH, 1, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))       AS THE_DAY_1_MONTH_AGO,
  DATEADD(MONTH, 2, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))       AS THE_DAY_2_MONTH_AGO,
  DATEADD(MONTH, 3, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))       AS THE_DAY_3_MONTH_AGO,
  DATEADD(MONTH, 4, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))       AS THE_DAY_4_MONTH_AGO,
  DATEADD(MONTH, 5, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))       AS THE_DAY_5_MONTH_AGO,
  DATEADD(MONTH, 6, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))       AS THE_DAY_6_MONTH_AGO,

  DATEADD(MONTH, -1, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))      AS THE_DAY_1_MONTHS_AHEAD,
  DATEADD(MONTH, -2, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))      AS THE_DAY_2_MONTHS_AHEAD,
  DATEADD(MONTH, -3, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))      AS THE_DAY_3_MONTHS_AHEAD,
  DATEADD(MONTH, -4, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))      AS THE_DAY_4_MONTHS_AHEAD,
  DATEADD(MONTH, -5, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))      AS THE_DAY_5_MONTHS_AHEAD,
  DATEADD(MONTH, -6, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))      AS THE_DAY_6_MONTHS_AHEAD,

  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 2, 0)                  AS FIRST_DAY_2_MONTHS_AGO,
  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 3, 0)                  AS FIRST_DAY_3_MONTHS_AGO,
  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 4, 0)                  AS FIRST_DAY_4_MONTHS_AGO,
  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 5, 0)                  AS FIRST_DAY_5_MONTHS_AGO,
  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 6, 0)                  AS FIRST_DAY_6_MONTHS_AGO,
 
  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 2, 0)                  AS FIRST_DAY_2_MONTHS_AHEAD,
  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 3, 0)                  AS FIRST_DAY_3_MONTHS_AHEAD,
  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 4, 0)                  AS FIRST_DAY_4_MONTHS_AHEAD,
  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 5, 0)                  AS FIRST_DAY_5_MONTHS_AHEAD,
  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 6, 0)                  AS FIRST_DAY_6_MONTHS_AHEAD,
                                                                        
  DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()) - 2, -1)                AS LAST_DAY_2_MONTHS_AGO,
  DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()) - 3, -1)                AS LAST_DAY_3_MONTHS_AGO,
  DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()) - 4, -1)                AS LAST_DAY_4_MONTHS_AGO,
  DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()) - 5, -1)                AS LAST_DAY_5_MONTHS_AGO,
  DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()) - 6, -1)                AS LAST_DAY_6_MONTHS_AGO,
 
  DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()) + 2, -1)                AS LAST_DAY_2_MONTHS_AHEAD,
  DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()) + 3, -1)                AS LAST_DAY_3_MONTHS_AHEAD,
  DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()) + 4, -1)                AS LAST_DAY_4_MONTHS_AHEAD,
  DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()) + 5, -1)                AS LAST_DAY_5_MONTHS_AHEAD,
  DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()) + 6, -1)                AS LAST_DAY_6_MONTHS_AHEAD

Fun with NULL

Leave a reply

I think this is interesting:

SELECT * FROM dual WHERE (1 BETWEEN 0 AND NULL)     -- FALSE
SELECT * FROM dual WHERE (0 BETWEEN 1 AND NULL)     -- FALSE
SELECT * FROM dual WHERE NOT (1 BETWEEN 0 AND NULL) -- FALSE
SELECT * FROM dual WHERE NOT (0 BETWEEN 1 AND NULL) -- TRUE

This has a good explanation however I am still embarrassed 🙂

How to Restore a Table in Oracle

Leave a reply

Simple and easy to use querying tool for Oracle SQL Tools has a little drawback. Trying to remove search string from the filter, you might hit Del. This does not remove the search string but drops the table. Of course it asks confirmation. But if you accidentally dropped the table, the following command might help:

FLASHBACK TABLE table_name TO BEFORE DROP;

The latest version of the tool (1.6) has a nice feature – read-only connection to database.

How to Generate Random Data in Oracle

Leave a reply

DROP TABLE t;
CREATE TABLE t (
  x VARCHAR(20),
  y NUMERIC(20,2),
  z INT
);

BEGIN
  FOR i IN 1..20000 LOOP
    INSERT INTO t VALUES (
      dbms_random.string('L', 20),
      dbms_random.Value,
      dbms_random.value(1,1000)
    );
  END LOOP;
  COMMIT;
END;
/

How to Kill Active Connections to SQL Server database

Leave a reply

ALTER DATABASE databasename SET SINGLE_USER WITH ROLLBACK IMMEDIATE

ALTER DATABASE databasename SET MULTI_USER

Creating Aggregate Tables

Leave a reply

This post demonstrates an example how to create aggregation tables in Kettle.

Let’s assume the original transformation loading delta is the following:

It reads data from source database, makes some transformations and loads data into data warehouse.

The source table JobEntry has two grouping fields project_id and employee_id and one additive measure registered_hrs. The task is to update aggregate tables (by project_id, and by employee_id) without complete rebuilding them.

Before updating the fact table in data warehouse, we need to retain the current value from the fact table (step Get previous fact value). After the fact table is updated, we are updating the aggregate tables. We calculate difference between the new and old value (step Calculate change), summarize the change of value to necessary granularity (steps Sort rows, Aggregate), and add the change to the value in the aggregate table (steps Get old agg.value, Calculate new value, Insert/update agg table). The transformation may look like this:

Demo Data (SQL Server)

The source database table:

create table JobEntry (
  project_id int,
  employee_id int,
  registered_hrs numeric(22,2)
)

The script changing the table (updating existing or inserting new rows):

declare
   @project_id int = RAND() * 3,
   @employee_id int = RAND() * 3,
   @registered_hrs numeric(22,2) = RAND() * 10

declare @cnt int = (
      select COUNT(*) from JobEntry
      where employee_id = @employee_id
      and project_id = @project_id
   )

if @cnt = 0
begin
   insert JobEntry values (
      @project_id,
      @employee_id,
      @registered_hrs
   )
end
else begin
   update JobEntry set registered_hrs = @registered_hrs
   where employee_id = @employee_id
   and project_id = @project_id
end

select * from JobEntry

The transformation

The data warehouse tables:

create table f_jobentry(
  project_id int,
  employee_id int,
  registered_hrs decimal(22,2)
);
create table f_jobentry_employee (
  employee_id int,
  registered_hrs decimal(22,2)
);
create table f_jobentry_project (
  project_id int,
  registered_hrs decimal(22,2)
);

Kettle transformation

Test

Create the necessary tables.
Run the script generating data.
Run the transformation updating the fact table and aggregate tables.
Check the output tables.
Run the script.
Run the transformation.
Check the output tables.

Summary

This approach seems too complex. Maybe the complete rebuilding the aggregate tables is not bad…

Use of Index and Characterset

Leave a reply

This post demonstrates influence of characterset on use of indexes. See also Language and Use of Indexes in Oracle

/*
DROP TABLE t;
ALTER SESSION SET NLS_LANGUAGE='AMERICAN';
*/

SELECT PARAMETER, VALUE FROM V$NLS_PARAMETERS;

CREATE TABLE t (x VARCHAR2(255));

INSERT INTO t VALUES ('a');
INSERT INTO t VALUES ('b');
INSERT INTO t VALUES ('c');
INSERT INTO t VALUES ('d');

CREATE INDEX t_idx ON t (x);

-- the index is used
UPDATE t SET x='a' WHERE x='a';

ALTER SESSION SET NLS_LANGUAGE='CZECH';

-- the index is still used if NLS_CHARACTERSET=WE8MSWIN1252
-- the index is not used if NLS_CHARACTERSET=WE8ISO8859P1
UPDATE t SET x='a' WHERE x='a';

bukhantsov.org

my technical notes

Category Archives: Databases

Creating ODBC system DNS with command line

Example of an Indexed View in SQL Server

How to Allow Remote TCP/IP Connections for SQL Server

Misc. Dates – SQL Server

Fun with NULL

How to Restore a Table in Oracle

How to Generate Random Data in Oracle

How to Kill Active Connections to SQL Server database

Creating Aggregate Tables

Demo Data (SQL Server)

The transformation

Test

Summary

Use of Index and Characterset