Data, data en nog eens data

Author: tom

  • Teradata: what columns to choose as Primary Index?

    —

    by

    tom
    in

    A major question with Teradata tables is what columns to choose when a primary index must be created. I understand that 4 different arguments might be used: 1: Is the column often accessed? If it is often accessed, best usage is made of the distibution of records over the different amps. 2: Is the column…

  • Teradata and fall back

    —

    by

    tom
    in

    I understand Teradata has a so-called fall back option. The idea is that data are stored twice; each record being stored to two different amps. I saw a nice picture that descibes the situation. Each record (Row 1, Row 2 etc) are linked to an amp. It is shown in below scheme as yellow boxes.…

  • Dynamic SQL

    —

    by

    tom
    in

    I must confess I never heard of Dynamic SQL. But suddenly around me everyone started talking on Dynamic SQL. What is dynamic SQL? Dynamic SQL is a SQL statement that is created at runtime. Such is the definition. I realised that I had already created quite some dynamic SQL without realising that such construction is…

  • MOLAP and ROLAP

    —

    by

    tom
    in

    I currently work in an organisation that has a debate on whether to use MOLAP or ROLAP. But first of all: what is discussed here? ROLAP and MOLAP are two different techniques to store data that are meant for OLAP analysis. In ROLAP the data are stored in tables in a relational database and each…

  • Duplicate records in teradata tables

    —

    by

    tom
    in

    Teradata offers the user the choice whether of not a check is made on duplicate records. Let’s first look at some code that allows duplicate records to be inserted. The code below has two elements that enables duplicate records: it contains a primary index that is not unique. the table is a multiset table. CREATE…

  • The Teradata answer on materialised views

    —

    by

    tom
    in

    Teradata has a feature that is designed to increase the performance of queries. This feature is called the “join index”. Such a join index is a structure that stores the outcomes from a query. These outcomes are stored permanently and they wait for the moment when they are called. The syntax of such a join…

  • Soft RI in Teradata

    —

    by

    tom
    in

    Teradata has the concept of “Soft RI”. In this concept, a foreign key is created but its restriction is not enforced. What happens in that situation? Let’s look at the normal situation of referential integrity. Suppose, we have two tables. One table is referred to by a second table. If a foreign key is created,…

  • Different codepages in SQL Server

    —

    by

    tom
    in

    It is possible in SQL Server to create columns in a table that have different codepage. This can be shown in the table below. That table has two attributes: char_unicode with a unicode codepage and char_latin that has a Latin codepage with West European characters only. The char_latin cannot contain characters like ł, İ or…

  • Codepages

    —

    by

    tom
    in

    Recently, I encountered the issue of codepages again. The problem was that the name of a Turkish city (İstanbul) was not represented correctly. Again a codepage issue. The character İ was not part of the application. How did these codepages come around? In the early days of Windows 95, we had ASCII. This had 127…

  • Running scripts in teradata

    —

    by

    tom
    in

    Let us assume, we have a data warehouse in teradata. Let us suppose that this data warehouse is loaded with a set of scripts. In principle it could be done. The question then arises how the scripts are run. An example could clarify this. The example looks like: .logon oTDD001.s2.ms.****.com/TOM.VAN-MAANEN, pau26688 .export report file=C:\Users\TOM.VAN-MAANEN\phi.txt .set…