dtexec returns immediately without any messages I'm trying to setup a command-line call to dtexec to validate packages, using the project model.  I'm calling dtexec like this:

dtexec /val /proj my.ispac /pack my.dtsx  /rep v

Though the real paths to the ispac and dtsx are longer.  WHen I run this, all I see on the console is:

Microsoft (R) SQL Server Execute Package Utility
Version 11.0.7001.0 for 32-bit
Copyright (C) 2016 Microsoft. All rights reserved.

Started:  10:40:01 AM
PS D:\>

The whole thing runs in under a second which leads me to believe that it did nothing.  And no messages of any kind are issued., even though I asked for verbose reporting.

Any ideas out there as to how to make this work?

You are almost there, the syntax you are using is correct by you cannot use an abbreviation /Val. From the official documentation, you can see that:

/Rep[orting] level [;event_guid_or_name[;event_guid_or_name[...]]
/Pack[age] PackageName: (Optional). Specifies the package that is executed.
/Rep[orting] level [;event_guid_or_name[;event_guid_or_name[...]]
/Proj[ect] ProjectFile: (Optional). Specifies the project from which to retrieve the package that is executed.
Validation and execution phase: The package is run, or validated without running if the /VALIDATE option was specified.

Also note that when executing package from a project don't add the package extension .dtsx
Based on that try to use the following command:
dtexec /Proj my.ispac /Pack my  /Rep V  /VALIDATE


Should Database Servers Be Defragmented? I've never been clear on this point - should Windows servers running SQL Server ever be defragmented with a tool such as Smart Defrag or Windows' Disk Defragmenter?

Provided you recon 100% that physical disk fragmentation is significantly affecting sql server performance and you can afford downtime, go for disk defrag.

I have never had to defrag a windows disk hosting sql server databases. You should choose a sensible autogrowth value for your databases and have Instant file initialization enabled.


Median with GROUP BY Suppose the following table t1:

=================
|  tag  |  val  |       --+ for the sake of simplicity, val is non NULL
=================
|   a1  |  v1   |
|   a1  |  v2   |
|   a1  |  v3   |
|   a1  |  v4   |
|   a1  |  v5   |
|   a2  |  v6   |
|   a2  |  v7   |
|   a2  |  v8   |
|   a2  |  v9   |
|   ... | ...   |
=================

If you execute the script below in MySQL:
SELECT `tag`, AVG(`val`) FROM `t1` GROUP BY `tag`

You would get the average values grouped by the column tag:

=================
|  tag  | AVG() |
=================
|   a1  | avg1  |
|   a2  | avg2  |
|   a3  | avg3  |
|   a4  | avg4  |
|   ... |  ...  |
=================

Besides AVG(), MySQL has several other built in functions to calculate aggregate values (e.g. SUM(), MAX(), COUNT(), and STD()) that could be used in the same way as in the script aforementioned. However, there is no built in function for median.
This issue has already come up several other times at SE; however, most of them related to tables without GROUP BY. The only one with GROUP BY seems to be MySql: Count median grouped by day; however, the script seems to be overcomplicated.
Question
What would be an easy and simple way (if possible) to calculate this median?
Follow-up
Excellent article that complement the accepted answer:
http://danielsetzermann.com/howto/how-to-calculate-the-median-per-group-with-mysql/

This query could answer your question: median value and group by

            SELECT tag, AVG(val) as median
            FROM
            (
              SELECT tag, val,
                  (SELECT count(*) FROM median t2 WHERE t2.tag = t3.tag) as ct,
                  seq,
                  (SELECT count(*) FROM median t2 WHERE t2.tag < t3.tag) as delta
                FROM (SELECT tag, val, @rownum := @rownum + 1 as seq
                      FROM (SELECT * FROM median ORDER BY tag, val) t1
                      ORDER BY tag, seq
                    ) t3 CROSS JOIN (SELECT @rownum := 0) x
                HAVING (ct%2 = 0 and seq-delta between floor((ct+1)/2) and floor((ct+1)/2) +1)
                  or (ct%2 <> 0 and seq-delta = (ct+1)/2)
            ) T
            GROUP BY tag
            ORDER BY tag;

I tried it on this dataset (mainly from here):

            +------+------+
            | tag  | val  |
            +------+------+
            |    1 |    3 |
            |    1 |   13 |

... (see explanation below)

            |    3 |   12 |
            |    3 |   43 |
            |    3 |   15 |
            +------+------+

and the result was:

            +------+---------+
            | tag  | median  |
            +------+---------+
            |    1 | 23.0000 |
            |    2 | 22.0000 |
            |    3 | 15.0000 |
            +------+---------+

Explanation

Inner subqueries will be computed first: sequence is (1)(2)(3)(4).

-- (4) compute the average (of 2 lines or 1 line)

    SELECT tag, AVG(val) as median
      FROM
      (

-- (3) get lines to compute the median value

        SELECT tag, val,
           (SELECT count(*) FROM median t2                    -- +number of lines for the current tag value as ct
              WHERE t2.tag = t3.tag) as ct,
           seq,
           (SELECT count(*) FROM median t2                    -- +number of lines before the current tag value as delta
              WHERE t2.tag < t3.tag) as delta                --     to compute the starting line number of a tag
         FROM (

-- (2) sort dataset by tag and sequence

                SELECT tag, val,
                    @rownum := @rownum + 1 as seq       -- +@rownum enable to create a sequence from 0 by 1
              FROM (

-- (1) sort dataset by tag and value

                    SELECT * FROM median
                    ORDER BY tag, val) t1

-- (2) continue here

              ORDER BY tag, seq
            ) t3 CROSS JOIN (SELECT @rownum := 0) x            -- +use to set @rownum to 0 (no data)

-- (3) continue here

         HAVING (ct%2 = 0                                      -- +when ct is even, select the two lines around the middle
                  and seq-delta between floor((ct+1)/2)
                                and floor((ct+1)/2) +1)
           or (ct%2 <> 0                                       -- +when ct is odd, select the one line in the middle
                  and seq-delta = (floor(ct+1)/2))
      ) T

-- (4) continue here

      GROUP BY tag
      ORDER BY tag;

Dataset:

        after (1)     after (2)           processing (3)
    +------+------+
    | tag  | val  |  ct  delta  seq       seq-delta
    +------+------+
    |    1 |    3 |  15    0     1        1         ct : odd ct%2 <> 0
    |    1 |    5 |  15    0     2        2         floor((ct+1)/2) : 8
    |    1 |    7 |  15    0     3        3
    |    1 |   12 |  15    0     4        4
    |    1 |   13 |  15    0     5        5
    |    1 |   14 |  15    0     6        6
    |    1 |   21 |  15    0     7        7
    |    1 |   23 |  15    0     8        8 ---> keep this line
    |    1 |   23 |  15    0     9        9
    |    1 |   23 |  15    0     10       10
    |    1 |   23 |  15    0     11       11
    |    1 |   29 |  15    0     12       12
    |    1 |   39 |  15    0     13       13
    |    1 |   40 |  15    0     14       14
    |    1 |   56 |  15    0     15       15

    |    2 |    3 |  14    15    16        1         ct : even (ct%2 = 0  )
    |    2 |    5 |  14    15    17        2         floor((ct+1)/2) : 7
    |    2 |    7 |  14    15    18        3         floor((ct+1)/2)+1 : 8
    |    2 |   12 |  14    15    19        4
    |    2 |   13 |  14    15    20        5
    |    2 |   14 |  14    15    21        6
    |    2 |   21 |  14    15    22        7 ---> keep this line
    |    2 |   23 |  14    15    23        8 ---> keep this line
    |    2 |   23 |  14    15    24        9
    |    2 |   23 |  14    15    25        10
    |    2 |   23 |  14    15    26        11
    |    2 |   29 |  14    15    27        12
    |    2 |   40 |  14    15    28        13
    |    2 |   56 |  14    15    29        14

    |    3 |   12 |  3     29    30        1                  ct : odd ct%2 <> 0
    |    3 |   15 |  3     29    31        2 ---> keep        floor((ct+1)/2) : 2
    |    3 |   43 |  3     29    32        3
    +------+------+

Dataset after (3)

    +------+------+------+------+-------+
    | tag  | val  | ct   | seq  | delta |
    +------+------+------+------+-------+
    |    1 |   23 |   15 |    8 |     0 |
    |    2 |   21 |   14 |   22 |    15 |
    |    2 |   23 |   14 |   23 |    15 |
    |    3 |   15 |    3 |   31 |    29 |
    +------+------+------+------+-------+

Outer query will compute the avg(val) group by tag value.

Hope this helps.

But what about median computing when there are null values? See EDIT2 below

Alternative: using a function

    DELIMITER //
    CREATE FUNCTION median(pTag int)
        RETURNS real
           READS SQL DATA
           DETERMINISTIC
           BEGIN
              DECLARE r real; -- result
    SELECT AVG(val) INTO r
    FROM
    (
      SELECT val,
           (SELECT count(*) FROM median WHERE tag = pTag) as ct,
           seq
        FROM (SELECT val, @rownum := @rownum + 1 as seq
              FROM (SELECT * FROM median WHERE tag = pTag ORDER BY val ) t1
              ORDER BY seq
            ) t3
            CROSS JOIN (SELECT @rownum := 0) x
        HAVING (ct%2 = 0 and seq between floor((ct+1)/2) and floor((ct+1)/2) +1)
          or (ct%2 <> 0 and seq = (ct+1)/2)
    ) T;
    return r;
    END//
    DELIMITER ;

But the function will be called for each row:

SELECT tag, median(tag) FROM median; -- my test table is 'median' too...

This query will be "better":

select tag, median(tag)
  from (select distinct tag from median) t;

That's all I can do! Hope it helps!

EDIT2 : about null values in data (column val in the example)

null values show be omited from the source data using a WHERE clause : WHERE val IS NOT NULL, in both 2 subqueriés that count lines and the subquery that gets data.

EDIT3 (LAST EDIT) : change the initialisation of the @rownum position

It should by put at the deepest level : so that it declared the soonest in the excution of the query.

DELIMITER //
CREATE FUNCTION median(pTag int)
    RETURNS real
       READS SQL DATA
       DETERMINISTIC
       BEGIN
          DECLARE r real; -- result
SELECT AVG(val) INTO r
FROM
(
  SELECT val,
       (SELECT count(*) FROM median WHERE tag = pTag and val is not null)     as ct,
       seq
    FROM (SELECT val, @rownum := @rownum + 1 as seq
          FROM (SELECT * FROM median
                       CROSS JOIN (SELECT @rownum := 0) x -- INIT @rownum here
             WHERE tag = pTag and val is not null ORDER BY val
          ) t1
          ORDER BY seq
       ) t3
    HAVING (ct%2 = 0 and seq between floor((ct+1)/2) and floor((ct+1)/2) +1)
      or (ct%2 <> 0 and seq = (ct+1)/2)
) T;
return r;
END//
DELIMITER ;

That is the same for the query.

Test with 2 data sets more :

|    4 | NULL |
|    4 |   10 |
|    4 |   15 |
|    4 |   20 |
|    5 | NULL |
|    5 | NULL |
|    5 | NULL |
+------+------+

39 rows in set (0.00 sec)

+------+--------------+
| tag  | median2(tag) |
+------+--------------+
|    1 |           23 |
|    2 |           22 |
|    3 |           15 |
|    4 |           15 |
|    5 |         NULL |
+------+--------------+
5 rows in set (0.08 sec)


Recovery model from Full to simple and Shrink transact log result on poor performance I'm new mantaining databases, I'm more like programmer but you know in some jobs you must be multitools because theres no specific dba.

My boss told me to change the recovery model from Full to simple because there was no specific routine to back up log and store the backups and the result was a 1.5 gb transaction log with no other initial backups (or maybe missing, the guy how do the project left) besides they do not care about the possible dataloss on daily basis. So I followed the command.

We schedule a windows and I do the job on SSMS and do the following steps:

Make a full copy to database with -> Tasks Copy Databases.
Make a full and transact log copy on Database.
Go to database options and change Recovery model from full to simple.
Shrink the transaction log ->Tasks > Shrink > Files

In the Shrink file dialog the currently allocate space field was the 1.5gb but were about 1 gb the Avaliable free space so I left 600MB to the log file

Reboot...

So after this the information was able to query but the performances is bad as hell a stored procedure that take 0.6 sec to execute before the changes now take up to 12 secs to run.

Moreover I repeat this in database copies (from the copy that I do before changes) and realized that instead of shrink the space in disk it's growing exactly the space trying to shrink.

After this I try to see if was fragmentation and indeed there about the 40% of tables about up to 50% in avg_fragmentation_in_percent

SELECT
    OBJECT_NAME(object_id) AS tblName
    ,dbo.index_name(object_id, index_id) AS ixName
    ,avg_fragmentation_in_percent
FROM
    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL)
WHERE
    avg_fragmentation_in_percent > 20
    AND index_type_desc IN('CLUSTERED INDEX', 'NONCLUSTERED INDEX')
ORDER BY
    avg_fragmentation_in_percent DESC

So I try to reorganize the index on the tables. Some go down to 20% but must still on ~ 45%

Finnaly I realised that the CPU was overheating more and more until reach 100% of CPU usage.

Here I used this query to make the what querys are cosuming more resources

SELECT TOP 50
     [Average CPU used] = total_worker_time / qs.execution_count,
     [Total CPU used] = total_worker_time,
     [Execution count] = qs.execution_count,
     [Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,
     (CASE WHEN qs.statement_end_offset = -1
        THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
      ELSE qs.statement_end_offset END -
    qs.statement_start_offset)/2)
    ,[Parent Query] = qt.text
    ,DatabaseName = DB_NAME(qt.dbid)
FROM
    sys.dm_exec_query_stats qs
CROSS APPLY
    sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY
    [Average CPU used] DESC;

Can anyone tell me what's happend?

Seen this happen a bunch.  Your first mistake was to shrink the transaction log.  When you set the recovery model to Simple the log is automatically truncated after a full backup of the Database.

What you have now is a combination of Index Fragmentation and Statistics needing to be rebuilt.  I would high recommend Ola Hallengren's free utility that can do both for you.

The following works for me after creating the procedures:

EXECUTE dbo.IndexOptimize
 @Databases = 'USER_DATABASES',
 @FragmentationLow = NULL,
 @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
 @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
 @FragmentationLevel1 = 5,
 @FragmentationLevel2 = 30,
 @UpdateStatistics = 'ALL'

The second thing that happened is most likely your query cache got purged and it now has to build new statistics and has cause parameter sniffing on your queries.  Rebuilding your statistics should help.


Database design: nested tagging I have a situation that after an initial thought ended in the following tables:

section:         id, name
section_tag:     id, name
section_tag_map: section_id, tag_id

item:         id, name
item_tag:     id, name
item_tag_map: item_id, tag_id

section_item_map: section_id, item_id

This describes well the data: items belong to sections. Sections and items have multiple tags for each of their "predicates", but they are different kinds of tags (section tags are specific to sections, item tags to items).

I will need to do this kind of query:

Select sections with section_tag's (3, 4). Easy.
Select items with item_tag's (1, 2) that belong to a section with section_tag's (3, 4). It didn't take long to realize what a mess of queries this requires.

I'm planning to simplify the initial idea, setting a single tag table and through app logic make items "inherit" tags that are meant to be set for sections. So it will be more like this:

section: id, name
item:    id, name
tag:     id, type, name

section_item: section_id, item_id
section_tag_map: item_id, tag_id
item_tag_map: item_id, tag_id

The upside is that queries will be much simpler. The downside is that I'll duplicate data and have to take care of syncing item tags when the tags from a section change.

What I'm weighting is if I should worry about having a monster query like this (first design):

SELECT
    i.*
FROM
    item AS i
INNER JOIN
    section_item_map AS sim
ON
    sim.item_id = i.id
INNER JOIN
    (SELECT
        s.*
    FROM
        section AS s
    INNER JOIN
        section_tag_map AS stm
    ON
        stm.section_id = s.id
    WHERE
        stm.tag_id IN (3, 4)
    GROUP BY
        s.id
    HAVING
        COUNT(s.id) = 2
    ) AS s
ON
    sim.section_id = s.id
INNER JOIN
    item_tag_map AS itm
ON
    itm.item_id = i.id
WHERE
    itm.tag_id IN (1, 2)
GROUP BY
    i.id
HAVING
    COUNT(i.id) = 2

...or a much simpler one like this (second design):

SELECT
    i.*
FROM
    item AS i
INNER JOIN
    item_tag_map AS itm
ON
    itm.item_id = i.id
WHERE
    itm.tag_id IN (1, 2, 3, 4)
GROUP BY
    i.id
HAVING
    COUNT(i.id) = 4

Am I on the right path or maybe I am missing something crucial here? The system is a single-user Sqlite app with about 20k sections and 100k items.

I don't see much difference in the two designs. Whether you have 2 tables for the tags or 1 table with a type attribute (that can take 2 values), you basically going to need the same (in matters or complexity and structure) queries.

What is different is your decision to materialize the relationship between items and the section_tags, the tags that are "inherited" to them through the relationship with the sections. But that can be done with either the first or the second design. And yes, it would simplify querying (on the expense of making harder to have the materialized tables/views in sync.)

So, about the choice of design, if as you say the item and the section tags are two different types of tags, either design would be ok and I don't think it would make much difference, except on the details in the interaction between applications and the database.

For the materialization part of question, I think the best would be to test both options. If you are to make a decision fast, you could take the first path. Keep the design simple and only if you have efficiency issues that can't be overcome, examine the option of materialization as it would be quite complicated (as you have already thought) to keep it synced.


Alternative for OPTIMIZER_FEATURES_ENABLE I've installed a PHP driven application in a client's 9i server (Oracle9i Release 9.2.0.1.0 - 64bit Production). Certain queries are having an awful performance (it can use from 15 minutes to hours just to calculate the execution plan!) and I've tracked the issue to a non-default value of the OPTIMIZER_FEATURES_ENABLE parameter: the default value for 9i is 9.2.0 but the customer changed it to 8.1.7. When I make the same change in my development box, I experience the same performance issues.

If they were running Oracle 10 or greater, I could change it myself for my own sessions but in 9i it's a static parameter that needs to be set for the whole instance. The change was made some time ago in order to support a very important legacy program. The client is currently waiting for an answer from the third-party supplier but I have the feeling that there's little chance of having it changed.

So, what are my options if the param needs to remain untouched? Can its effects be emulated with other changeable settings? Any other idea?

Because you have OPTIMIZER_FEATURES_ENABLE at 8.1.7, OPTIMIZER_MAX_PERMUTATIONS is defaulting to a very high value. Fortunately this parameter is dynamic so you can set it just for your session without affecting your legacy application.


why is the Log File is 5 times the database overnight when deleting? The log file alone is  close to 500 GB while the database is 80 GB.
This is not a production system so I tried to delete a table which had 780,000 records. this table has 5 individual indexes that are sorted in ascending order, when i tried to delete 1 record with the PK in a clustered index, it got to 20 minutes before I cancelled it.

I left ssms running in "delete From Table" and now the log has exploded to 500GB.I canceled the query and now the database is in recovery. I cant delete anything from this table and its not the IO since i can delete millions from other tables (months ago, this growth is from this one delete).  The waits showed a lot of CXPackets and PageLatch_EX
but im trying to understand if there is a setting that its giving me a row versioning overhead. Read commited snapshot isolation is on, but DB snapshot is off.

now the recovery will take hours but what im planning to do it drop the indexes , update the statistics and drop the contraints so that i can try to tuncate but i see myself back here again. Should i delete at batches of 2000 and back up the log to clear it? remember one delete took more than 20 minutes and the only locked transactions are for the pagelatches. I included the query plan for the delete, im worried that somehow the parallel queries have hammered the log file harder that it needs too, any insight would save me loads of time, thanks in advance.

If this is a one off and this isn't production, you could put the database into simple recovery mode, do all the deleting you need to do.

Shrink the log file at this point if you don't want it to be 500GB.

Then put it back into its previous recovery mode.


Is there a way to 'mark' a single row in a table and automatically unmark others? Is there a way to flag one row in a database table (MariaDB/MySQL mostly, but I just want to know), so that there is only one row in a table that has that flag? If I set that flag (for eg. 'active') for any other row, then the row which currently has the 'active' flag is automatically unset. The idea is to not have to manually unset the older 'active' row when there's going to be a new one. I just insert/update a row with 'active'=1 and it's automatically the only one.

To clarify my intent, I'm not looking for a method of doing this manually - I'm trying to find out if there is a built-in feature in a database system, since this is a pretty common use case. Hence, the post in DBA, instead of SO. I don't even know what tags to use for this question.

Wrong solution.

Have another table with one row that you keep changing.  It contains the PRIMARY KEY of the one marked row.  (Delete the row if you want to have no rows 'marked'.)  JOIN the two tables to get the marked rows.


SGA_MAX_SIZE and SGA_TARGET We recently increased sga_max_size from 2g to 4g but didn't increase sga_target. Is it OK not to increase sga_target? The reason we wanted to increase sga is to reduce wait time on sequential reads in our db. Should we increase sga_target too?

Below is what we currently have.

NAME           TYPE       VALUE          DISPLAY_VALUE
sga_max_size    6         4194304000     4000M
sga_target      6         1996488704     1904M

It is perfectly valid to increase the SGA_MAX_SIZE without changing the SGA_TARGET though it probably isn't going to change your performance.  Setting the SGA_MAX_SIZE to a larger value allows you to adjust the SGA_TARGET upward without restarting the database.  It won't affect the actual amount of RAM allocated to the SGA, that's controlled by the SGA_TARGET.  If your intention was to increase the size of the SGA from 2 GB to 4 GB, you need to adjust the SGA_TARGET.


Why do row-versioning isolations use a linked list of versions of record? Trying to better understand row-versioning based transaction isolations in SQL Server...

According to Kimberly L. Tripp, Neal Graves's "SQL Server 2005 Row Versioning-Based Transaction Isolation":

"...when a record in a table or index is modified, the new record is stamped with the "sequence_number" of the transaction that is performing the modification"

which makes perfect sense - when a transaction ID is linked to a record change.

What I cannot understand is, from the same source:

"... All the earlier versions of a particular record are chained in a linked list"

Why is there a need in a linked list, i.e. to link the changes of the same record ?

UPDATE:

As stated by Remus Resanu in his answer to "Does Snapshot Isolation only give you what has been committed? (And General Snapshot Isolation questions.)":

"Snapshot will give you the data that was committed at the moment the snapshot was taken. This moment means either when you issued BEGIN TRAN if you use true SNAPSHOT isolation level, or the moment your statement started if you use read_committed_snapshot RCSI"

In other words, there is a need to keep multiple row versions of a record for  multiple/different transactions (with corresponding pointers/links), that is to be accessed directly to a corresponding copy taken when snapshot was taken (though multiple ones per multiple transactions but the single one per one corresponding transaction). This is in perfect agreement with explanation that RCSI takes less space in tempdb but contradicts to statement (or makes ambiguous to my comprehension), referring to both RCSI and SNAPSHOT (note, that it is not making any difference between RCSI and SNAPSHOT according to its context used in that article):

"All the earlier versions of a particular record are chained in a linked list; and in the case of long-running row versioning–based transactions, the link will have to be traversed on each access to reach the transactionally consistent version of the row. Version records must be kept in the version store only as long as there are row versioning–based queries that might be interested in them"

The latter sounds like the space required is still the same both for RCSI and SNAPSHOT?!
and it is stated contradictory in different MSDN articles

SUBQUESTION:

In case of multiple updates of the same record in a single transaction, how many versions are being stored?

Why is there a need in a linked list, i.e. to link the changes of the same record?

The "current row" in the index or data page provides a pointer to only the most recent row version in the version store. A transaction that needs older versions needs a way to navigate to the exact version it should use. The linked list provides this mechanism.


Where do I set the I/O size for DB2 9.7 LUW driver (AIX 6.1)? I am looking to change the I/O size returned from the DB2 server for query results. (As per below instruction from SAP BODS 4.X performance optimization guide)

  Increase the size of each I/O from the database server to match the OS
  read-ahead I/O size.

Where do I actually change the parameters for the database ? Is it a driver configuration parameter or at server level? Also I have no clue where to check the OS read ahead block size(AIX 6.1, DB2 9.7 LUW)

The communication buffer size for remote clients is determined by the RQRIOBLK database configuration parameter (more info in the manual).

However, the statement you quote is overly simplistic and does not make much sense for DB2 configuration. Firstly, DB2 will perform direct disk I/O, so the OS read-ahead setting, if such thing even exists, will not have any effect on DB2. Secondly, DB2 will determine prefetch size, which is what you might call the DB2's answer to read-ahead, dynamically during each query compilation, so the number of pages read with each I/O will be different for different tables and even different queries against the same table. Thirdly, if your query has a WHERE clause, not all that is read from disk will be returned to the client, obviously, so the prefetch size will hardly matter at all.

You'll be better off, instead of trying to follow some very vague and generic advice, determine the actual bottleneck in your system and address that.


calculating max and joining tables Just need a little help as new to SQL.

I have a table which contains readingtime (records every 4 seconds during the day) and an energy reading. I wanted to know how much energy was used in a day.
this is the query that i used

SELECT date(readingtime), max(energy)-min(energy)as total
FROM lights_1
WHERE readingtime between 20140407000000 and 20140409235959
GROUP BY date(readingtime) LIMIT 0,30;

This gave me the desired results for 1 table. I have 4 tables of these lights_2, Lights_3, lights_4 and i would like to display them together and hence match up the reading time so each day has the energy requirements for all for tables with the total. When i try and join tables i get a lot of errors!

Can anyone help.

thanks

Union gives

date(readingtime) total
2014-04-07        1244
2014-04-08        1039
2014-04-09        1389
2014-04-07        419
2014-04-08        1102
2014-04-09        482

I would like

date(readingtime) total_1  total_2
2014-04-07        1244      419
2014-04-08        1039      1039
2014-04-09        1389      1389

SELECT  date(lights_1.readingtime),
        max(lights_1.energy)-min(lights_1.energy)as total_1,
        max(lights_2.energy)-min(lights_2.energy)as total_2
FROM lights_1
join lights_2 on lights_1.readingtime = lights_2.readingtime
WHERE lights_1.readingtime between 20140407000000 and 20140409235959
GROUP BY date(lights_1.readingtime) LIMIT 0,30


RDS MySQL 8.0.16 has empty "INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS" table On aws RDS I have an mysql 8.0.16 instance running.
My problem is now, that the INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS table is empty.

This is a problem for me because I would like to create the following table:

CREATE TABLE my_table
(
    ...
    coordinates            POINT         NOT NULL SRID 4326
    ...
);

But this leads to an error message that says There's no spatial reference system with SRID 4326.

Whats going on here? Why is the table empty? I would expect it to contain at least one entry for the SRID 4326.

The new MySQL v8.0.16 image on RDS (as of writing this) is missing spatial references other than SRID 0.

As a workaround, you can create a new instance with v8.0.15 and then perform the minor upgrade (requires reboot).

select count(*) from INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS

...returns:

 5152


Giving selective permissions in a database I have a user [AD\dbusersgroup_limitedrights] that should have on two schema's
[ICD] and [XCD] within a database only SELECT and EXECUTE rights, however for each of the users in the [AD\dbusersgroup_limitedrights] I have created a schema with their corp initial

[DIS]
[SUB]
[TEC]

within these schemas they should have the rights to create their own tables, views and stored procedures, that are based on the [ICD] and [XCD] schemas objects.

I made the group now owner of the schema's [DIS],[SUB],[TEC] and on the other two schemas did grant them right for SELECT and EXECUTE. I also did make the group db_role db_datareader to make sure they don't get rights on other later created schema's.

However even though they are owner of their own schema they can't create objects within the schemas. I assume that this is due to the db role db_datareader.

So for such a scenario, what is best practice to give limited permissions within a database?

You'll have to grant them appropriate permissions at the database level to indicate what they should be able to do. Then grant ALTER permission (or make them the owner) on the schemas in which they can do these things.

GRANT CREATE TABLE, CREATE PROCEDURE TO [AD\dbusersgroup_limitedrights]
GRANT ALTER ON SCHEMA::[DIS] TO [AD\dbusersgroup_limitedrights]


Replicated MySQL write performance query Whilst I've worked with MySQL for years, I've never had to created a replicated database, and after researching the topic I have a query about performance.

I'm thinking of an architecture based on 5 servers, one of them designated as the master and will handle ALL write transactions, whilst the other 4 will handle read requests.

My question is, if the master server is at 90% CPU usage handling writes, are the other servers in the cluster also under the same strain due to each write being sent to all other servers? or is something going on where the bulk of the processing is handles on the master so the slaves do less work processing the write transaction?

This depends on the query and the binary log format.

An UPDATE query that fetches lots of rows and changes very few will in binlog_format=ROW end up as UPDATE table SET x=y WHERE PK=z so will have a lower CPU/IO overhead. If it was using binlog_format=STATEMENT exactly the same query would end up on the slave.

Conversely a UPDATE query that updates a large number of rows, UPDATE table SET status=Fixed WHERE date < NOW() will generate a large number on binary log entries (for each primary key) and might be slower in binlog_format=ROW than statement.

There's a good document on this in the mysql manual

Based on your galera tag, and the known limitations, you'll be using binlog_format=ROW and each table will have a primary key. Take note regarding transaction size so to avoid stalls due to large updates.


SQL Server Management Studio SSMS 18.0 General Availability release (GA) install fails Requirement

I wish to install SSMS 18.0 GA (https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-2017), on Windows 10 but get error:

Uninstall Preview

Attempt to uninstall 18.0 preview via Add/Remove programs:

Uninstall fails with:

"The feature you are trying to use is on a network resource that is unavailable"

"Click OK to try again, or enter an alternate path to a folder containing the installation package 'vs2017_isoshell_for_ssms.msi' in the box below"

Use source C:\ProgramData\Package Cache{D620DE9C-8A83-4BA5-83A8-E709A4150A47}v15.0.27248.2015\redist\

vs2017_isoshell_for_ssms.msi does not exist on my machine.

Other fixes attempted

I attempted to remove via other means:

I have run MicrosoftProgram_Install_and_Uninstall.meta.diagcab from https://support.microsoft.com/en-us/help/17588/windows-fix-problems-that-block-programs-being-installed-or-removed
And removed everything with SSMS or management studio in the name. After that, SSMS 18.0 preview still appears in add/remove programs and an uninstall gives the same error as above.
msiexec /x “{D620DE9C-8A83-4BA5-83A8-E709A4150A47}” brings up the same dialog so I cannot progress:

Reinstall SSMS 18.0 Public Preview 6 works fine from https://go.microsoft.com/fwlink/?linkid=2052501
Try and install SSMS 18 GA again and get a different error of:

Remove SSMS 18 preview 6 via Add/Remove programs, and get the same error:

Question

How can I remove SSMS 18 preview? I don’t have vs2017_isoshell_for_ssms.msi, a web search for this file gives nothing and a re-install of the preview version does not provide it.

From the command line, I was able to uninstall a different 18.0 preview (I don't have 6 anywhere):

msiexec /x "{2051D5D4-618D-4B9F-BA64-C1AE19003F2E}"

But I did not have the error about the shell msi, and I'm not sure if the CLSID changed, so I'm not sure that will work for you. (I'm not convinced the GUID in the dialog you're getting is the one you need for SSMS.)

Stolen shamelessly from my own blog post about uninstalling Evaluation Edition which fails if the product has expired.


"Invalid option" while creating a tablespace Can someone tell me what is wrong with this syntax:

CREATE TABLESPACE ACME_DATA
DATAFILE '/oracle/d02/acme/ACME_DATA_01.dbf'
SIZE 40M
EXTENT MANAGEMENT LOCAL
AUTOEXTEND ON NEXT 2M
MAXSIZE 100M
UNIFORM SIZE 128KB
SEGMENT SPACE MANAGEMENT AUTO;

Error report:

SQL Error: ORA-02180: invalid option for CREATE TABLESPACE
02180. 00000 -  "invalid option for CREATE TABLESPACE"

The autoextend option belongs to the datafile specification (one per datafile), not to the tablespace itself, and the uniform option is part of the extent management clause, so you've got a mixup there.

Try:

CREATE TABLESPACE ACME_DATA
DATAFILE
  '/oracle/d02/acme/ACME_DATA_01.dbf' SIZE 40M AUTOEXTEND ON NEXT 2M MAXSIZE 100M
EXTENT MANAGEMENT
  LOCAL UNIFORM SIZE 128K
SEGMENT SPACE MANAGEMENT
  AUTO;

(Note: size units are K, M, G, not KB or similar.)


Postgresql: replica and HA I've postgresql 9.4.

I'm aware that there are many answer on DBA and SO, I'm aware of the wiki and the documentation.

But anyway, I can't understand in a simple way what's the right way to have a master postgresql server, and a slave postgresql server that is updated from the master (as soon as possible, to reduce data loss).

Also I would need a way to do automatic promotion of the slave to master in case of failing master.

Should I use Repmgr? Or I should just use Postgresql's functions?

Thanks

The right way is setting hot-standby.

Using Repmgr is just a question of your convenience. You can use it, or you can setup trigger file creating, or promoting with pg_ctl, for example. It's your choice. Repmgr is not involved in process of streaming data from master to slave, it is just management tool.

Also,

Be careful with 'automatic promotion of slave in case of failing master'. You should definitely know what you are doing and you should define 'failing' very strictly. If you don't have a super-large cluster, then it is the best not to have failover fully automated.
Be careful about 'data loss'. You should know, that streaming replication will save you only in case there is some failure on server side, or something wrong with network/disk/etc. It won't help you if someone will come to your db and type DROP DATABASE. Changes from statements will be immediately streamed to slave and the DB will be lost everywhere. The only way to avoid data loss from SQL statements is a valid backup.


Is there a table size above which SELECT performance degrades significantly? In MySQL, does SELECT performance degrade significantly after a certain table size? I have a table with about 107 million rows and some queries seem stuck in "Sending data" for a long time. Of course, "sending data" is a catchall for all sorts of query processing according to the MySQL manual. This is puzzling because EXPLAIN says that the query is "using where; using index" and only returns about 1700 rows. It's the only query running, so I know it's not a locking issue.

Would it help to split this table into a few hundred smaller tables, if I'm splitting on something that's already part of the key? It seems like the key itself should be better than that solution, but I'm at a loss as to why this query should take so much time.

The table engine in question is TokuDB, but I see the same results with InnoDB. I haven't tried MyISAM or any other engine with this table.

The query is (apologies for obfuscation due to internal data):

SELECT id FROM my_table WHERE my_end_time>=1234567890 AND
    my_start_time<=2345678901 AND a='XXX' AND b='YYY'
    ORDER BY my_start_time ASC;

The key it's using is a UNIQUE index on

a, b, my_start_time, my_end_time

Not sure what version of TokuDB you are running, or what MySQL/MariaDB distribution, but there is an index condition pushdown bug that occurs on queries similar to the one you've provided. We are still waiting for a fix, the only current workaround is to disable ICP when running the problematic query.

Try running the query with ICP turned off. To disable it, issue the following command in the session just before running the query:

SET optimizer_switch='index_condition_pushdown=off';

And please report back success/failure, and the particular versions of TokuDB and MySQL/MariaDB.

(full disclosure, I'm an employee of Tokutek, makers of TokuDB)


SQL Server 2012 catalog.executions to sysjobhistory - any way to join them? I have exhausted my resources and can't find a foolproof way to join the ssisdb.catalog tables to the jobs that run them. Trying to write some custom sprocs to monitor my execution times and rows written from the catalog tables, and it would be greatly beneficial to be able to tie them together with the calling job.

I'm trying to find out if there are any tables that can be used to join the catalog.executions table to msdb.sysjobhistory. I need to relate which job called the SSIS package and then use the data in executions for my analysis.

Not for any case.
But I have some way:

It is designed for only for the case in the message table sysjobhistory, appears string:

  "Execution ID: xxxxxxx".

In the following query, in the last column I extract the ExecutionId.

In the case ExecutionId has a value, it can join:

SELECT *
FROM (
    SELECT
        h.step_name,
        h.message,
        h.run_status,
        h.run_date,
        h.run_time,
        SUBSTRING(h.message, NULLIF(CHARINDEX('Execution ID: ', h.message),0)+14 ,PATINDEX('%[^0-9]%',SUBSTRING(h.message, NULLIF(CHARINDEX('Execution ID: ', h.message),0)+14 ,20))-1) ExecutionId
    FROM MSDB.DBO.SYSJOBHISTORY h) history
LEFT JOIN
SSISDB.CATALOG.EXECUTIONS ex on ex.execution_id = history.ExecutionId

Note that can also attach these tables:

LEFT JOIN SSISDB.CATALOG.OPERATION_MESSAGES om on history.ExecutionId = om.operation_id
LEFT JOIN SSISDB.CATALOG.EVENT_MESSAGES em on em.operation_id =history.ExecutionId


Translate SQL Server update statement to MySQL I have a development environment with MySQL and a production environment with SQL Server.

This update statement works with SQL Server:

UPDATE THIS
SET <list_of_column_assignments>
FROM <table_name> THIS
JOIN ( VALUES (<csv1>, <csv2>,...) ) VALS ( <column_names> )
ON <list_of_primary_keys_comparison>

How do I translate this to a MySQL statement?

I'd like to have the same code for both environments (if possible).

Example that works on SQL Server:

UPDATE THIS
SET THIS.DSEndGeoSys = VALS.DSEndGeoSys,
    THIS.DSPrecisaoGeoSys = VALS.DSPrecisaoGeoSys,
    THIS.NRLatGeoSys = VALS.NRLatGeoSys,
    THIS.NRLngGeoSys = VALS.NRLngGeoSys
FROM TBEstabelecimento THIS
JOIN
(
    VALUES
        (
            'Icém - SP, 15460-000, República Federativa do Brasil',
            'APROXIMADA', '06291846001771', -20.4053212, -49.1625989, '1'
        )
) VALS ( DSEndGeoSys, DSPrecisaoGeoSys, NRCGCCEICPF, NRLatGeoSys, NRLngGeoSys, TPInscricao )
ON
(
    THIS.TPInscricao = VALS.TPInscricao
    AND THIS.NRCGCCEICPF = VALS.NRCGCCEICPF
)

MySQL requires a distinct approach: An INSERT with ON DUPLICATE KEY UPDATE.
Example above works like this:

INSERT INTO TBEstabelecimento (
    DSEndGeoSys, DSPrecisaoGeoSys, NRCGCCEICPF, NRLatGeoSys, NRLngGeoSys, TPInscricao
)
VALUES  (   'Icém - SP, 15460-000, República Federativa do Brasil',
            'APROXIMADA', '06291846001771', -20.4053212, -49.1625989, '1'
) ON DUPLICATE KEY UPDATE
DSEndGeoSys = VALUES(DSEndGeoSys),
DSPrecisaoGeoSys = VALUES(DSPrecisaoGeoSys),
NRCGCCEICPF = VALUES(NRCGCCEICPF),
NRLatGeoSys = VALUES(NRLatGeoSys),
NRLngGeoSys = VALUES(NRLngGeoSys),
TPInscricao = VALUES(TPInscricao)

Of course, the big benefit of such approach is when you have several VALUES like (csv1), (csv2), ... so you can do the whole update with only one command


mysqldump: How to include index type? When I dump the schema of my database

mysqldump --no-data my_db > schema.sql

I get lines like this

KEY `index_tbl_on_col` (`col`)

but I want lines like this

KEY `index_tbl_on_col` (`col`) USING BTREE

The indexes in question do, in fact, have Index_type = 'BTREE'.

show index from tbl;
| Table | Key_name      | Index_type |
+-------+---------------+------------+
| tbl   | ix_tbl_on_col | BTREE      |

I have tried a variety of mysqldump options including using --skip-opt --create-options together.

I am using mysqldump 5.6.26 from homebrew.

mysqldump --version
mysqldump  Ver 10.13 Distrib 5.6.26, for osx10.10 (x86_64)

You should not worry about embedding the USING BTREE onto the indexes. Why ?
The default index type for MyISAM and InnoDB is BTREE. You cannot impose any other type.
The default index type for MEMORY Storage Engine is HASH. You can impose a BTREE index type.
I wrote a post 4 years ago on HASH not being allowed for MyISAM and InnoDB: Why does MySQL not have hash indices on MyISAM or InnoDB?
In light of this, there is no need to explicitly tack on USING BTREE.
You can look up the CREATE INDEX Documentation to verify default index types per Storage Engine.
UPDATE 2015-09-24 16:23 EDT
Hey Jared, I got a surprise for you. I went Googling around and there is a way to spit out USING BTREE. The option is --compatible=mysql40. I saw it in StackOverflow : MySQL error USING BTREE
Interestingly, there is sort of a bug report on this. You know who submitted it ? A guy named Jared.


best way to retrieve data using multiple parameters in Cassandra? I have created a table that contains the userid, the content that they posted on their newsfeed, the date of when they posted it, and a primary key(I am using nodejs)

my first shot at retrieving the data was me trying use an IN clause and passing an array as a paramater.

"SELECT * FROM SomeKeySpace WHERE userId IN (?) ALLOW FILTERING"

But then after some research I realized that IN clauses should be avoided especially when passing alot of parameters.

This is what my table looks. Id is the primary key

 id      | date        | content             | userid
---------+-------------+-------------------------+--------
 1427181 | 27-Apr-2018 |           sometext  |   5332

I am trying to retrieve the date and the content filtered by userids. In the attempt that i mentioned above I had retrieved all the userIds from another db and pushed them to an array then i passed it as a parameter.

This really depends on what other queries you're planning to execute - in Cassandra you need to model data around your queries.

For your specific question, you need either secondary index on userid, and do search (there are also some limitations there), or build second table with composite primary key consisting of userid, date, id - this would be especially useful if you'll have query "Show me all blog posts by this user".

Doing ALLOW FILTERING is generally is not recommended, because it may cause scanning of your whole cluster.


Alternatives to MySQL Workbench I've been running into some problems with MySQL Workbench that I'm not sure will be able to be fixed (namely, what appears to be an old bug that may have resurfaced), so I'd like to be able to find a good backup to it in the event that I can't use it. I've sifted through several suggested alternatives, such as Navicat (Navicat is not free for business use, it only has a 30-day trial), but none of them have satisfactorily met my current requirements.

What I need:

Linux compatible (preferably Linux native, I'm using Ubuntu)
Able to connect to remote databases via SSH tunneling
Free and/or Open Source (I currently don't have the funds to shell out $100+ for a product and would rather support a good Open Source project, anyway)
An interface at least as good a Workbench's (it could just be because I'm used to it, but I like its interface, very clean and rather intuitive tabbed interface; doesn't have to be identical, though)
Supports MySQL 5+

While it's nice to have, I don't need the extra data modeling stuff that Workbench has. I also don't need fancy GUI stuff (query designers and the like). A way to view the data in an orderly manner and a place to edit queries is really all I need.

I'm currently checking out HeidiSQL, but it reminds me of something I used to use when I was running Win9x/Win2k, only runs through WINE, has crashed on me once already, and just overall feels clunky, though I do like its easy ability to dump a database or do changes to multiple tables.

Believe it or not, although the products went EOL December 18, 2009, the last versions of MySQL Query Browser and MySQL Administrator are still available for download for free. Hey, I still use them today.

Click here and get it while Oracle isn't looking ... QUICK !!!


SQL (Redshift) SELECT COUNT from CSV column Hi exchange community,

Problem statement and details:
I am trying to query a column that contains CSV data to produce a count of instances of each value in the table.  There are a total of 14 possible values in the CSV data. These are static and will never change. Each record contains at least one value in the CSV column but may contain up to all 14 values. Each value may only appear at most once in the CSV column per record.

Sample Data from Pets column:
cat, dog fish, rat
dog
dog, cat horse, ferret, zebra
dog, cat, alligator, bear, fish, bird

what have i tried?
I've gotten as far as being able to return a view that contains a single record with columns containing a count for the number of instances. It's close to what I need, I just need to have this transposed (rows <-> columns).  any ideas?

SELECT
    count(case when vet.pets like '%dog%' THEN 1 ELSE NULL END) AS dog
    ,  count(case when vet.pets like '%cat%' THEN 1 ELSE NULL END) as cat
    ,  count(case when vet.pets like '%rat%' THEN 1 ELSE NULL END) as rat
    ,  count(case when vet.pets like '%bird%' THEN 1 ELSE NULL END) as bird
    ,  count(case when vet.pets like '%fish%' THEN 1 ELSE NULL END) as fish
    ,  count(case when vet.pets like '%hamster%' THEN 1 ELSE NULL END) as hamster
    ,  count(case when vet.pets like '%ferret%' THEN 1 ELSE NULL END) as ferret
    ,  count(case when vet.pets like '%horse%' THEN 1 ELSE NULL END) as horse
    ,  count(case when vet.pets like '%zebra%' THEN 1 ELSE NULL END) as zebra
    ,  count(case when vet.pets like '%bear%' THEN 1 ELSE NULL END) as bear
    ,  count(case when vet.pets like '%alligator%' THEN 1 ELSE NULL END) as alligator
    ,  count(case when vet.pets like '%sugar_glider%' THEN 1 ELSE NULL END) as sugar_glider
    ,  count(case when vet.pets like '%tiger%' THEN 1 ELSE NULL END)  as tiger
    ,  count(case when vet.pets like '%rock%' THEN 1 ELSE NULL END) as rock
    FROM vet

This produces:

  dog | cat | rat | bird | fish | hamster | ferret | horse | zebra |
  bear | alligator | sugar_glider | tiger | rock
      3 | 5 | 2 | 6 | 8 | 1 | 8 | 5 | 1 | 3 | 2 | 2 | 4 | 2

But I need it in the format:

dog | 3
cat | 5
rat | 2
bird | 6
fish | 8
hamster | 1
ferret | 8
horse | 5
zebra | 1
bear | 3
alligator | 2
sugar_glider | 2
tiger | 4
rock | 2

Was able to figure this out by unioning my individual count statements.

SELECT 'dog' as 'animal', count(case when vet.pets like '%dog%' THEN 1 ELSE NULL END) AS 'count' FROM vet
UNION ALL
SELECT 'cat' as 'animal',  count(case when vet.pets like '%cat%' THEN 1 ELSE NULL END) AS 'count' FROM vet
UNION ALL
etc.


Raid 1 or Raid 1+0 for SQL Server Architecture We are looking at new servers.  Today our SQL Servers are built as follows:

C/D on RAID 1 ,
E : Tempdb on RAID 1,
F:log files on RAID 1 ,
G : Catalog data on RAID 1 ,
H: model data on RAID 1 ,
I : symbols on RAID 1,
J: DB maintenance on RAID 1 ,

So we have a lot of SSD’s, all on RAID 1 with no sharing.

We are considering changing to a large RAID 1+0.  However the concern is if we will end up with performance impacts since there are random reads that happen throughout the day but not as much as writes.

My question is - whether a large 1+0 RAID is suggested / recommended, which would reduce the quantity of SSD’s that we use and allow us to expand a single container easier or stick with single RAID 1’s?

RAID level 10(1+0) has been recommended by microsoft for high reads. Refer the doc: https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms190764%28v%3dsql.105%29

This level is also known as mirroring with striping. RAID 10 uses a striped array of disks that are then mirrored to another identical set of striped disks. For example, a striped array can be created by using five disks. The striped array of disks is then mirrored using another set of five striped disks. RAID 10 provides the performance benefits of disk striping with the disk redundancy of mirroring. RAID 10 provides the highest read-and-write performance of any one of the other RAID levels, but at the expense of using two times as many disks.

RAID levels higher than 10 (1 + 0) may offer additional fault tolerance or performance enhancements. These levels generally are proprietary systems. For more information about these types of RAID systems, contact the hardware vendor.


How to execute SQL query from two database I want to know how to execute sql query from two database, one database is a remote server and next one is my local database, so from both the database table I want to use JOIN query.

Is it possible to do in SQL server management studio 2016 ?

If it is possible please guide how to do so.

Thanks,
Manu.

As @Akina mentioned linked server might be a way to go. if you create linked server with name RemoteServer your query may look like:

  Select * from myLocalDatabase.dbo.myLocalTable  mlt
    join RemoteServer.RemoteDatabase.dbo.myRemoteTable mrt
    on mlt.col1 = mrt.col1

Be aware of performance penalty when running remote joins.
in certain cases you might be better importing data to local server and then joining it especially if this is going to repeat more often.

As for creation of linked server you could try this to add another SQL Server

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'RemoteServerDescription', @srvproduct=N'', @provider=N'SQLNCLI11', @datasrc=N'RemoteServername_or_IP', @catalog=N'databasename'

USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'RemoteServerDescription', @locallogin = NULL , @useself = N'True'
GO


installing mysql 5.1 alongside mysql 5.5 mysql 5.5 is installed. I need 5.1 to install an old website so it can be updated. The installer refuses to let me install onto the disk with 5.5. I don't have any other disks. I downloaded the package file DMG. Help? Can I get around this with a different kind of source file?

I'm no expert on Mac OS X, but on Linux, it's relatively easy to have more than one install running side by side.

I follow a procedure very much based on the one here.

Download the source code. Gunzip and untar it. As a sister directory to the unzipped and untarred one, create a directory (I call it "sandbox"). From within sandbox, run "cmake ../", then "make" and then "make package". Then I copy the package up a level, gunzip and untar it and run the ./scripts/mysql_install_db --defaults-file=.

It's the --defaults-file (i.e. my.cnf) that's critical. In there you override the normal defaults - otherwise multiple MySQLs on the same machine will just overwrite each other's settings. I've appended a sample of my own file below. I just put all the extra stuff into the --basedir directory and that normally works a treat.
Things like the socket, pid-file and, of course, the port need to be changed.

You may be able to just run the binary install script with the --defaults-file and get your system up and running without having to compile, but as I said, I'm not that familiar with OS X.

[EDIT]

Forgot to mention that you have to kick off the daemon with --defaults-file (my.cnf) also.

From your base-dir issue the command

./bin/mysqld --defaults-file=<full_path_to_my.cnf>

============== sample --defaults-file =====================

pol@pol-laptop02:~/Downloads/mysql/src/mysql-5.6.19-linux-x86_64$ more my.cnf
[mysqld]
#
# * Basic Settings
#
user        = pol
pid-file    = /home/pol/Downloads/mysql/src/mysql-5.6.19-linux-x86_64/mysqld.pid
socket      = /home/pol/Downloads/mysql/src/mysql-5.6.19-linux-x86_64/mysql.sock
port        = 3306
basedir     = /home/pol/Downloads/mysql/src/mysql-5.6.19-linux-x86_64
datadir     = /home/pol/Downloads/mysql/src/mysql-5.6.19-linux-x86_64/data
tmpdir      = /tmp
pid-file    = /home/pol/Downloads/mysql/src/mysql-5.6.19-linux-x86_64/mysql.pid
lc-messages-dir = /home/pol/Downloads/mysql/src/mysql-5.6.19-linux-x86_64/share
lc-messages = en_US

general_log     = on
general_log_file= /home/pol/Downloads/mysql/src/mysql-5.6.19-linux-x86_64/logfile.txt

log_error   = /home/pol/Downloads/mysql/src/mysql-5.6.19-linux-x86_64/error.log
log-error   = /home/pol/Downloads/mysql/src/mysql-5.6.19-linux-x86_64/error.log

slow_query_log_file = /home/pol/Downloads/mysql/src/mysql-5.6.19-linux-x86_64/slow_query.log
slow_query_log  = 0

explicit-defaults-for-timestamp = TRUE

#language   = /home/pol/Downloads/mysql/src/mysql-5.6.19-linux-x86_64/share/english

# bind-address  = 0.0.0.0 - may not need c.f. host...

[client]
port        = 3306
socket      = /home/pol/Downloads/mysql/src/mysql-5.6.19-linux-x86_64/mysqld.sock
host            = 127.0.0.1
# or maybe localhost... cf. bind-address above...

# added from http://dev.mysql.com/doc/refman/5.6/en/load-data-local.html
# to allow for abrowse to load data!
#loose-local-infile =   1

local-infile    = 1

[mysqld_safe]
#user       = linehanp
#socket     = /home/pol/Downloads/mysql/src/mysql-5.6.19-linux-x86_64/mysql.sock
#err-log        = /home/pol/Downloads/mysql/src/mysql-5.6.19-linux-x86_64/error.log
#pid-file   = /home/pol/Downloads/mysql/src/mysql-5.6.19-linux-x86_64/mysql.pid
#log_error  = /home/pol/Downloads/mysql/src/mysql-5.6.19-linux-x86_64/error.log
#log-error  = /home/pol/Downloads/mysql/src/mysql-5.6.19-linux-x86_64/error.log

pol@pol-laptop02:~/Downloads/mysql/src/mysql-5.6.19-linux-x86_64$


Uniq join 3 tables with relationship table not uniq I am using Postgres.

I have 3 tables:

table_1
  id

table_1_2
  table_1_id, table_2_id

table_2
  id

The problem is the relationship table table_1_2 is not uniq.

I.e., table_1_2 will have the same tuples multiple times:

{1,1}, {1,1}, {1,1}, {1,1}, {1,1}, {1,2}, {1,2}, {1,2} etc.

Is there a way to join the table_1 and table_2 using table_1_2, but with only uniq result?

Currently, I will get all the repeated items as from the relationship table.

There are many ways. Here is one (should be a fast variant to retrieve all rows):

SELECT t1.*, t2.*
FROM   table1 t1
JOIN  (
    SELECT DISTINCT table_1_id, table_2_id
    FROM   table1_2
    ) t1_2 ON t1_2.table_1_id = t1.id
JOIN   table2 t2 ON t2.id = t1_2.table_2_id;

Why not remove the duplicates permanently? And add a UNIQUE constraint ...


SSAS Model Refresh - Not enough memory to complete this operation Error We have started encountering an issue regarding the refresh of our tabular SSAS model.

The tabular SSAS model has 38 tables within it.

This process has been running without issue for over a year, however for around a month now, we have not been able to sucessfully process the tables within the model.

If i access the SSAS database > Right Click > Process Database > Select the mode to Process Default followed by OK, this is when the problem occurs.

It will sit there for around 5 minutes before failing with the error messsage:

Failed to save modifications to the server. Error returned: 'There's not enough memory to complete this operation. Please try again later when there may be more memory available.

If i try and 'process' the tables individually, i recieve the same error message too.

I have looked into the memory settings for SSAS, within the advanced window and have reset the values to their defaults. So the key values (as im aware of) are currently:

The server has been rebooted several times, we still have the same problem.

Environment Details:

Windows Server 2016 Datacenter

SQL Server 2017 (RTM-CU9-GDR) (KB4293805) - 14.0.3035.2 (X64)

SSAS Version: 14.0.223.1

Server Mode: Tabular

Server Memory: 64Gb

SQL Server Assigned Memory: 28Gb

I have ready multiple articles online regarding these sorts of problems, however nothing seems relevant / useful so far.

Any guidance / assistance would be greatly appreciated.

Disclaimer: I am not a BI / SSAS guy. Im just a DBA who has been given this problem to look at, so forgive me if i dont quite explain this correctly.

TL/DR: Add more Memory, reduce the size of your Model(s), and/or move either the SQL Server Services or SQL Server Analysis Services to a different server (e.g. scale out)

Longer Explanation:
We went through this exercise a few months back with our Tabular SSAS production server, and actually reached out to Microsoft for "formal" recommendations as our Infrastructure team was being stingy with RAM (which I can understand as it's not exactly cheap).  Just for clarity's sake, the error we ran into was as follows:

  The operation has been cancelled because there is not enough memory available for the application. If using a 32-bit version of the product, consider upgrading to the 64-bit version or increasing the amount of memory available on the machine.

Our server was originally setup with 64GB of memory and was hosting 2 SSAS models totaling 40GB in size.  No other SQL Server services were hosted on this machine.  Some days our models would process without issue, but most days they would fail.  We would reboot the server and then maybe they would succeed... if the wind was just right and the stars and planets all aligned.

Unlike Multidimensional (MOLAP/ROLAP/HOLAP) models, the default Tabular Models are loaded entirely into memory as they leverage In-Memory technology.  If the model(s) are unable to be loaded entirely into memory, you run into problems.

Sadly, Microsoft's documentation breaks down on what the "memory recommendations" are as I cannot find any formal document providing anything other than "minimum" levels that are needed to just run the service.  From the support ticket we filed, Microsoft's recommendations were as follows:

  For a model of size X, provision between 2X - 10X RAM on the SSAS server to be used by the SSAS service, which is further influenced by the following factors:

  Cube Processing requires 2X - 3X RAM for full processing which includes a shadow copy of the model in builtin memory.
  The number of Users/Reports connected to the cube also increase RAM requirements, at times up to 10X depending upon number of reports, volume, etc. as users/reports can generate DAX queries which perform calculation or memory materialization (which cause the engine to build an intermediate non-compressed result and can cause the memory consumption to go higher than expected).
  The number of Models being processed can also increase the memory footprint required.
  Enable the VertiPaqPagingPolicy if the setting is disabled, so SSAS can utilize the OS paging file for additional memory at the cost of processing and query performance.

What we ended up doing was increase the amount of RAM on our server, which ultimately solved our issues for the time being.  The only other real alternative "solutions" is to limit the amount of data you need in your model(s) or scale out (e.g. move services to another server) your deployment to other servers.

What I suspect is happening in your case is that your SSAS service is running out of memory because your SQL Server Service is also hosted on the same server.  Basically you need to either segregate these services from one another or have enough RAM on the server to let them run in parallel.  I would highly suggest segregating your SSAS services to a different server if possible, but licensing challenges may impact this so be sure to have enough RAM.

Other things you can fiddle with are the config settings located in the msmdsrv.ini file, but for our scenario, we didn't have much success with these making any significant differences in the eventual outcome of running out of memory.


Two primary keys to one foreign key racing_couple
ID (PRIMARY)

breeding_couple
ID (PRIMARY)

egg
IDpar(FOREIGN KEY)

I have 2 IDs from 2 different tables (racing_couple and breeding_couple) and they are primary keys. In 3. table (egg) I have IDpar which is foreign key and references ID in racing_couple  and breeding_couple. Tables represents racing and breeding couple of pigeons, table "egg" represents egg of racing or breeding couple. And I'm using IDpar to identify which egg belongs to which couple.

Note There are other fields in both database but they are mostly varchar and not so relevant to this problem.

If I have something like this in both databases. How to know which IDpar has value from racing_couple and which IDpar has value from breeding_couple.
I think I made mistake by making my database like this, but is there any way to make that work?

ID(racing_couple)
1
2

ID(breeding_couple)
1
2

IDpar(egg)
1 (ID racing_couple)
1 (ID breeding_couple)
2 (ID racing_couple)
2 (ID breeding_couple)

I would suggest that you either want a single "couple" table instead of two, if the properties of each coupling are sufficiently similar, or a couple table that the two existing types of couple "inherit" from, like so:

RC                  Couple                                Egg
----------          ---------------------------           ----------------
C ID (FK)   ----->  C ID (PK)                    <-----   C ID (FK)
(rc props)    |     (generic couple properties)           (egg properties)
              |
BC            |
----------    |
C ID (FK)   --'
(BC props)

You could still have breeding and racing details for any could in this arrangement (which might actually be fine, of course) but each egg can only be associated with one couple. You won't have problems with overlapping ID ranges as there is only one range of IDs for couples when modeled this way, and all other tables with data relating to couples only have the one FK linking to the could record.


Case insensitive search of CLOBs in Oracle 12 Question: What is the most performant way to do a case-insensitive search  in a CLOB column?

I've simplified the query to easily reproduce the problem. The filedata table can have millions of rows. Many rows can have the same fileid. The fileid column is indexed. In cases where a particular fileid matches many rows, the proc is really slow.

select * from filedata where fileid=2148102100 AND REGEXP_LIKE (dataClobfield, 'Some Text Here', 'i');

This query completes in 130 seconds. 16 rows are returned by this query, but there 400k rows matching the fileid value (in other words, if I removed the regexp_like where clause, I would get 400k with a fileid of 2148102100).

If I change this to

select * from filedata where fileid=2148102100 AND dataClobfield like '%Some Text Here%';

It completes in 32 seconds but the search is case-sensitive, which does not meet the requirements.

I've also tried

select * from filedata where fileid=2148102100 AND LOWER(dataClobfield) like '%some text here%';

This returns in 83 seconds... better than the regexp_like, but still not great.

Other things I've read about haven't worked: setting the session NLS_SORT=BINARY_CI (doesn't work with CLOBs), using the DBMS_LOB.instr function (case sensitive). Ideally I'd like to get this down to 30 seconds. Any ideas?

Searching without any kind of index seems to be not a good idea. And an analysis without further details is difficult. But you can add resources to improve your speed.
It seems that the bottleneck of your query is CPU and not the disks. If disk speed is the limitation there would not be a difference between regexp and LIKE.

You can try to parallelize your query. If the work is done by 4 processes instead of one, it is possible that the query response tim is a quarter of the original time.
you can add an additional clob field that contains the content of dataClobfield  in uppercase. this field is used for searching the records you are interested in


enable logging of password-like patterns in mysql client history From MySQL 5.6.8 onwards, statements containing the pattern *PASSWORD* or *IDENTIFIED* are not logged in the mysql command line client history. I know I can add to this list with --histignore or MYSQL_HIST_IGNORE, but how do I defeat this filtering altogether? I'm working on a development server, never storing cleartext passwords and above all, I did not ask for this behaviour. It's my server, and I want to be able to cursor-up and get a previous query without cutting and pasting.

I posted a previous question on this topic when I couldn't work out why some queries were not being logged, but now we're on 5.6.22 and I'm wondering if anyone has found a solution.

Working on a project with db columns like "password_hash", this behavior was at first very confusing and then over time, simply maddening.  There appears to be no way to disable this.

Here's an evil hack that I only resorted to out of desperation after other workarounds failed:

cp /usr/bin/mysql ~/bin     # copy to local bin dir
vim ~/bin/mysql             # vim can edit binary files
/PASSWORD                   # search for the offending string
RPWXUK4FD [ESC]             # overtype w/ string unlikely to ever match
:wq                         # save & quit

This assumes that you already have a ~/bin directory and it is listed before /usr/bin in your $PATH


Improving distinct values estimates in Postgres Full counts in Postgres can be slow, for reasons that are well-understood and much discussed. So, I've been using estimation techniques instead, where possible. For rows, pg_stats seems fine, for views, extracting an estimate returned by EXPLAIN works okay.

https://www.cybertec-postgresql.com/en/count-made-fast/

But what about distinct values? Here, I have had a lot less luck. Sometimes the estimates are 100% correct, sometimes they're off by factors of 2 or 20. Truncated tables seem to have badly stale estimates in particular (?).

I just ran this test and have provided some results:

analyze assembly_prods; -- Doing an ANLYZE to give pg_stats every help.

select 'count(*) distinct' as method,
        count(*) as count
from (select distinct assembly_id
      from assembly_prods) d
union all
select 'n_distinct from pg_stats' as method,
        n_distinct as count
from pg_stats
where tablename  = 'assembly_prods' and
      attname    = 'assembly_id';

The results:

method                      count
count(*) distinct           28088
n_distinct from pg_stats    13805

That's only off by a factor of 2, but I've seem much worse in my data. To the point where I won't use estimates. Is there something else that I can try? Is this something that PG 12 improves?

Follow-up

I hadn't ever experimented SET STATISTICS before, because there are only so many hours in a day. Inspired by Laurenz' answer, I've take a quick look. Here's a useful comment from the documentation:

https://www.postgresql.org/docs/current/planner-stats.html

  The amount of information stored in pg_statistic by ANALYZE, in
  particular the maximum number of entries in the most_common_vals and
  histogram_bounds arrays for each column, can be set on a
  column-by-column basis using the ALTER TABLE SET STATISTICS command,
  or globally by setting the default_statistics_target configuration
  variable. The default limit is presently 100 entries. Raising the
  limit might allow more accurate planner estimates to be made,
  particularly for columns with irregular data distributions, at the
  price of consuming more space in pg_statistic and slightly more time
  to compute the estimates. Conversely, a lower limit might be
  sufficient for columns with simple data distributions.

I have often got tables with a few common values and a lot of rare values. Or the other way around, so the right threshold will depend. For those who haven't used SET STATISTICS, it lets you set the sampling rate as a target number of entries. The default is 100, so 1000 should be higher fidelity. Here's what that looks like:

ALTER TABLE assembly_prods
    ALTER COLUMN assembly_id
    SET STATISTICS 1000;

You can use SET STATISTICS on a table or index. Here's an interesting piece on indexes:

https://akorotkov.github.io/blog/2017/05/31/alter-index-weird/

Note that the current documentation does list SET STATISTICS on indexes.

So I tried out thresholds of 1, 10, 100, 1000, and 10,000 and got these results out of a table with 467,767 rows and 28,088 distinct values:

Target   Estimate  Difference  Missing
     1   13,657    14,431      51%
    10   13,867    14,221      51%
   100   13,759    14,329      51%
 1,000   24,746     3,342      12%
10,000   28,088         0       0%

Obviously you can't draw any general conclusions from one case, but SET STATISTICS looks pretty darn useful and I'll be glad to have it in the back of my mind. I'm tempted to raise the target a bit in general as I suspect it would help in many of the cases in our system.

First, a remark: your query could be written simpler as

SELECT count(DISTINCT assembly_id) FROM assembly_prods;

Also, your statistics query is wrong, because n_distict can also be negative. You should query:

SELECT CASE WHEN s.n_distinct < 0
            THEN - s.n_distinct * t.reltuples
            ELSE s.n_distinct
       END AS n_distinct
FROM pg_class t
   JOIN pg_namespace n ON n.oid = t.relnamespace
   JOIN pg_stats s ON t.relname = s.tablename
                      AND n.nspname = s.schemaname
WHERE s.schemaname = 'public'
  AND s.tablename = 'assembly_prods'
  AND s.attname = 'assembly_id';

For a simple query like that, the statistics should contain a good estimate.

If the estimates are off, try to ANALYZE the table. That will also fix the results for a newly TRUNCATEd table. TRUNCATE does not cause PostgreSQL to autoanalyze the table (there may be room for improvement here).

If that improves the results, see that the table is analyzed more often by configuring

ALTER TABLE assembly_prods SET (autovacuum_analyze_scale_factor = 0.05);

It is also possible to set autovacuum_analyze_scale_factor to 0 and raise autovacuum_analyze_threshold to the daily change rate for the table.

If ANALYZE alone does not improve the estimate, increase the size of the sample:

ALTER TABLE assembly_prods ALTER assembly_id SET STATISTICS 1000;

A new ANALYZE should now produce better estimates.

Getting good n_distinct estimates for more complicated queries becomes increasingly more difficult. Sometimes extended statistics will improve the estimate considerably.

As far as I know, PostgreSQL v12 does not bring any improvements in this area.


Update Table from SP in SSIS I know there are similar questions to this one, but I could not find one that answered my question.

Inside my current SSIS package...
I have a dataflow task which...
Takes output from a stored procedure (contains about 10k rows) on the db
Inserts that output into a table in the dbo

The problem is that I have 2 choices of doing this (otherwise I break primary key restrictions)

1.) Truncate the table before I insert data to it

this is problematic because what if the SP fails for some reason? Other procedures use this table and depend on it to have data.

2.)Use "Update" command to update 1 row at a time for ~10k rows

This ties up resources on our server, which is already doing some heavy lifting just to execute the stored procedure (yes, it is a poorly written SP, but I digress).
I looked into doing a row count then checking the count variable before truncating, but I couldn't figure out how to make that work inside a dataflow with data stemming from a stored procedure.

So there HAS to be a way I don't know of to easily take my data and ONLY update existing records or insert new records.

P.S. SQL Server 2014

Current SSIS:

OLE DB Source = EXEC somesp.sp

|

Count = Count rows (used later on, irrelevant to task at hand)

|

Sort = Sorts for data integrity to ensure nothing will break my primary key (data coming from the sp)

|

Destination = Place records from sp result into a destination table

What you described is a classic UPSERT pattern, named after the dual intention of Updating some records and Inserting others.

T-SQL offers the MERGE statement which can be used and has good performance. Not always the easiest to write/maintain.

SSIS options:

1) UPSERT via SSIS - Essentially what you do is identify your update rows and store them in a temp table so that you can handle Updates via an Execute SQL task in the SSIS Control Flow instead of the row by row OLEDB command in the data flow.
Essential needs are to ID your update rows and send them to the TMP table destination. Add an Execute SQL task after your data flow and update as a set. You will see huge performance increase over the OLEDB command task and this may be all you need. Handle inserts as normal into the destination table.

Source Query vs Existing table join if existing then determine if an update is needed otherwise insert. You can either blindly update or in my case I have a quick join if they exist, and another check to see if there is actually an update... you can do this all in a single step depending on your data/join conditions.

2) DELETE/INSERT via SSIS - Very similar to #1, but instead of performing an update you use two SQL tasks to first delete the update rows then insert new versions of them. All non-update rows get written as normal inserts in your data flow.


Analysis of MySQL database in real time Amazon RDS similar to phpMyAdmin I'm using a single Amazon RDS instance (medium) to power several WordPress websites running on a single Amazon EC2 instance.

I used to have my databases on another EC2 instance and installed phpMyAdmin there.
phpMyAdmin offers quite neat run time analysis.

Is there anyway I can gain such analysis providing that I'm using Amazon RDS for my databases?

This depends on your particular setup, but you just modify your phpmyadmin config file to connect to the RDS database, and use phpmyadmin as usual.

Go to the phpmyadmin's root folder (usually /var/phpmyadmin) and copy config.sample.inc.php to config.inc.php .

Inside the file set up yout RDS connection:

/* Authentication type */
$cfg['Servers'][$i]['auth_type'] = 'cookie';
/* Server parameters */
$cfg['Servers'][$i]['host'] = '<RDS host>';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['compress'] = false;
/* Select mysql if your server does not have mysqli */
$cfg['Servers'][$i]['extension'] = 'mysqli';
$cfg['Servers'][$i]['AllowNoPassword'] = false;

Remember that the server where you are running phpmyadmin must have access to the RDS.


Is tuning the innodb_buffer_pool_size important on Solaris ZFS? We're running a moderate size (350GB) database with some fairly large tables (a few hundred million rows, 50GB) on a reasonably large server (2 x quad-core Xeons, 24GB RAM, 2.5" 10k disks in RAID10), and are getting some pretty slow inserts (e.g. simple insert of a single row taking 90 seconds!).

Our innodb_buffer_pool_size is set to 400MB, which would normally be way too low for this kind of setup. However, our hosting provider advises that this is irrelevant when running on ZFS. Is he right?

No, he's not right. If a block is not in the InnoDB cache, then it has to be fetched, which means it will come either from disk or from the ZFS cache, at which point two copies of it exist in main memory. If you use that block, it will come out of the InnoDB cache. If you write that block, it will go from the InnoDB cache to the disk. The ZFS cache is just a helpless spectator in this scenario.

However, if your issue is INSERT performance, it's unlikely to be related to this unless your system is under a very heavy load... is it? From the docs:

  If an index record should be inserted
  into a nonunique secondary index,
  InnoDB checks whether the secondary
  index page is in the buffer pool. If
  that is the case, InnoDB does the
  insertion directly to the index page.
  If the index page is not found in the
  buffer pool, InnoDB inserts the record
  to a special insert buffer structure.
  The insert buffer is kept so small
  that it fits entirely in the buffer
  pool, and insertions can be done very
  fast.

  Periodically, the insert buffer is
  merged into the secondary index trees
  in the database. Often it is possible
  to merge several insertions into the
  same page of the index tree, saving
  disk I/O operations. It has been
  measured that the insert buffer can
  speed up insertions into a table up to
  15 times.

  The insert buffer merging may continue
  to happen after the inserting
  transaction has been committed. In
  fact, it may continue to happen after
  a server shutdown and restart (see
  Section 13.2.6.2, “Forcing InnoDB
  Recovery”).

  Insert buffer merging may take many
  hours when many secondary indexes must
  be updated and many rows have been
  inserted

SHOW ENGINE INNODB STATUS should show you exactly what the DB is waiting on. Also see if strace can help.


Why is there ASYNC_NETWORK_IO wait type on Shared Memory connections? I have an app (windows service) local to the SQL Server box.  It calls a stored procedure on the SQL Server box that returns a ton of data and thus causes ASYNC_NETWORK_IO wait.

The database connection from is of Shared Memory type (I double checked for sanity). Why would there be a ASYNC_NETWORK_IO wait type on Shared Memory connection?

I thought ASYNC_NETWORK_IO had to do with a network lag?

As far as SQL Server is concerned, whenever results are going out the door, it's ASYNC_NETWORK_IO whether it's shared memory or TCP/IP.


Fill a GTT with external data each session I have to import an external csv file into a table in database each day. The file is encoded in 'UCS2-LE BOM'. I have found a solution using external tables and bulk insertion, but the problem is to re-use the GTT made as an external table to be filled by the new daily file, each day in a job.

Since the GTT already exists from previous session, I can't use CREATE GLOBAL TEMPORARY TABLE ... ORGANIZATION EXTERNAL .... I can however drop the table each time and re create it but I think there has to be a way I'm not aware of.

Additionally, It is much appreciated if the importing could be done entirely without the need for GTTs and loading a file straight into the destination table. I have done that before with custom tagged hierarchial XML files (Oracle is so powerful in XML files) but couldn't find many resources and references on csv files, since.

Thanks!

Edit:

GTT's can't be used as external tables

Error report -
SQL Error: ORA-30658: attempt was made to create a temporary table with EXTERNAL organization
30658.0000 -  "attempt was made to create a temporary table with EXTERNAL organization"
*Cause:    An attempt was made to create an External Organized Temporary table.
           This is not supported.
*Action:   Don't do that!

Edit2:

I found out that organization external doesn't create the table though it says on the log it does. The table is parsed each time from the blueprint defined in organization external on access.

I have to import an external csv file into a table in database each
  day.

  ...

  Additionally, It is much appreciated if the importing could be done
  entirely without the need for GTTs and loading a file straight into
  the destination table.

And that is what SQL*Loader was made for.

SQL> create table employee
(
  id integer,
  name varchar2(10),
  dept varchar2(15),
  salary integer
);

$ cat employee.csv
100,Thomas,Sales,5000
200,Jason,Technology,5500
300,Mayla,Technology,7000
400,Nisha,Marketing,9500
500,Randy,Technology,6000
501,Ritu,Accounting,5400

$ cat load.ctl
load data
 infile 'employee.csv'
 append
 into table employee
 fields terminated by ","
 ( id, name, dept, salary )

Load data into empty table:

$ sqlldr user/password@database control=load.ctl

SQL> select * from employee;

        ID NAME       DEPT                SALARY
---------- ---------- --------------- ----------
       100 Thomas     Sales                 5000
       200 Jason      Technology            5500
       300 Mayla      Technology            7000
       400 Nisha      Marketing             9500
       500 Randy      Technology            6000
       501 Ritu       Accounting            5400

Append data (notice the append keyword in load.ctl):

$ sqlldr user/password@database control=load.ctl

SQL> select * from employee;

        ID NAME       DEPT                SALARY
---------- ---------- --------------- ----------
       100 Thomas     Sales                 5000
       200 Jason      Technology            5500
       300 Mayla      Technology            7000
       400 Nisha      Marketing             9500
       500 Randy      Technology            6000
       501 Ritu       Accounting            5400
       100 Thomas     Sales                 5000
       200 Jason      Technology            5500
       300 Mayla      Technology            7000
       400 Nisha      Marketing             9500
       500 Randy      Technology            6000
       501 Ritu       Accounting            5400


How can I create 3 tables with references On SQL Server I started using SQL Server recently and I think this is simple to resolve.

I need to create one table with a country list, and two tables that will use the country list in a column.  Here is the code:

1 - (uf to log a country list)

CREATE TABLE uf
(
id int primary key,
uf varchar(2), (dont know if can I use a column with the same name of the table)
);

2 - the resumed table that will use the list.

CREATE TABLE carta
(
id int primary key,
uf varchar(2) not null,
);

3 - the resumed table that will use the list.

CREATE TABLE cliente
(
id int primary key,
uf varchar(2) not null,
);

I'm really thankful if someone helps me

If have some errors in the writing, I'm sorry, I'm from Brazil and don't know English very well

I've re-written your CREATE TABLE statements like this:

CREATE TABLE dbo.uf
(
    uf_id INT NOT NULL
        CONSTRAINT PK_uf
        PRIMARY KEY CLUSTERED
    , uf_name VARCHAR(50) -- you *can* name a column the same as the table
                         -- but try not to, since it will result in confusion
);

CREATE TABLE dbo.carta
(
    carta_id INT NOT NULL
        CONSTRAINT PK_carta
        PRIMARY KEY CLUSTERED
    , uf_id INT NOT NULL
        CONSTRAINT FK_carta_uf
        FOREIGN KEY REFERENCES
        dbo.uf(uf_id)
    , carta_name VARCHAR(50) NOT NULL
);

CREATE TABLE dbo.cliente
(
    cliente_id INT NOT NULL
        CONSTRAINT PK_cliente
        PRIMARY KEY CLUSTERED
    , uf_id INT NOT NULL
        CONSTRAINT FK_cliente_uf
        FOREIGN KEY REFERENCES
        dbo.uf(uf_id)
    , cliente_name VARCHAR(50) NOT NULL
);

Always specify the "schema" of the table; in the above example that is dbo - which is the default schema.  Specifying the schema name removes confusion about what schema will contain the table.  I've renamed the uf column in the uf table to uf_name to remove confusion when the uf table is used in queries.  You can name a column the same name as the table it appears in, but I would try not to do that.

The dbo.uf table has a PRIMARY KEY constraint which enforces uniqueness and makes the contents of the referenced column valid for use as a foreign key in other, related tables.

The dbo.carta and dbo.cliente tables have FOREIGN KEY references to the dbo.uf table.  This prevents inserting rows into those tables without a valid entry in the dbo.uf table.  This is commonly referred to as referential integrity and is an important principle in good database design.

I've made all the columns NOT NULL since non-null values are easier to understand, especially when learning.

Below, we're inserting data into the three tables in a way that shows how the foreign keys work.  First, we'll insert two country names into the dbo.uf table:

INSERT INTO dbo.uf(uf_id, uf_name)
VALUES (1, 'Brazil')
    , (2, 'Canada');

SELECT *
FROM dbo.uf;

The results:

The uf_id column is used in the other two tables, instead of the actual name of the country itself:

INSERT INTO dbo.carta (carta_id, uf_id, carta_name)
VALUES (1, 1, 'Natal') --Brazil
    , (2, 1, 'Fortaleza') --Brazil, again
    , (3, 2, 'Winnipeg'); --Canada

SELECT *
FROM dbo.carta;

INSERT INTO dbo.cliente (cliente_id, uf_id, cliente_name)
VALUES (1, 1, 'Paulo') --Brazil
    , (2, 2, 'Nedja') --Canada
    , (3, 2, 'Max'); --Canada, again

SELECT *
FROM dbo.cliente;

To join the three tables together, you use the JOIN clause, such as:

SELECT *
FROM dbo.uf
    LEFT JOIN dbo.carta ON uf.uf_id = carta.uf_id
    LEFT JOIN dbo.cliente ON uf.uf_id = cliente.uf_id

which shows the following results:

Steve Stedman made a fantastic poster about the different kinds of JOIN statements, which is available here.


How to sort the data fetched from horizontal partitioned tables I have a telco billing software system. In it there are daily logs of users' calls. The logs are horizontally partitioned by date (month). Each partition is stored in a separate database and may be spread over multiple instances.

In the UI the user will specify a date range. The data returned can be sorted on any field. The date range may span over multiple partitions. The application must support paging through the date range's data.

I cannot load too many records into memory for sorting. Putting sort inside the query only gives me sorted data inside one result-set.

So I need to sort data from multiple partitions which are each individually sorted. How can I return sorted records to the UI from multiple sorted result-sets?

It sounds like your architecture has gone past partitioning and is sharded. If you have system software or an application framework in place which can handle sharding that is where this requirement should be implemented.

In order to get the correct sort sequence you will have to have the results from all involved shards in the same place at the same time. There is no way to get some results from one shard and then get more results from another shard and be able to guarantee any sort sequence the user may choose.

One way (as BriteSponge suggested) would be to define a view which spans all the shards. Let the DBMS deal with the complexities of marshalling the various shards' results sets. This would be my preferred solution if it can be managed. It separates the application from the DB implementation details.

If you have to write this yourself there is some hope in the fact you have paging in place. This limits the maximum number or rows you will show in the UI and, therefore, the maximum number of rows that have to be returned from each shard. From the date range you can determine which shards are required. To each shard you submit a query of the form

select <columns>
from <table>
where date between <start> and <end>
order by <user selection>
fetch first <number> rows only

The <number> will be the maximum number of rows you want to show in a single page in the UI. While it is unlikely they will all come from a single shard it is possible so that's what the limit must be.

If your version of Oracle does not support the final limiting clause the following will still work, but will be much less efficient.

Perform a multi-way merge on the resultsets. Stop when you reach the limit for one page in the UI. Discard the remainder of the resultsets.

When the user pages forward, re-submit the SELECT using standard paging techniques.

When the user chooses a different sort sequence amend the SQL to match and resubmit to each shard.

My preference would be to implement this server side in a stored procedure. This will keep application and storage as separate as possible. Also, rather than a multi-way merge, the various resultsets can all go into a single working table which is then sorted and the correct number of rows returned to the application.

If you do implement it client-side, rather than discarding the unused portion of the resultsets they could be cached. This which may reduce the load for subsequent pages at the cost of additional complexity.


Block size is for Database or Tablespace? Forgive me I am just a newbie in the Oracle. I saw there is a db_block_size parameter which can be specified the block size for a database so that the whole database will be read and written data with the specified block size. But what make me confused is we can also specify block size for the Tablespace when we want to Create Tablespace. Please check the Block size clause. My question is are they contradictory? How could they both work in the same Database. Because the TableSpace belongs to a Database. Thanks.

db_block_size is the default block size of the database, but it does not necessarily mean all your tablespaces have the same block size, you can have tablespaces with different block sizes.

You need to define seperate cache areas for tablespaces with non-default block size. For example, if your db_block_size is 8192, and you want to create a tablespace with block size of 16384, first you need to specify a size for the "16K" cache with the parameter db_16k_cache_size, and after that you can create a tablespace with 16384 block size.


Is there such a thing as an OLAP cube that can change the number of levels in a hierarchy without having to be rebuilt? The current analytics solution that my team works with cannot handle changes in the structure of one of its hierarchies. If a new level or branch is added, then the entire domain must be first backed up, and then rebuilt with the new hierarchy.

One of our use cases requires an OLPA model where the domain should be able to gracefully handle changes to the hierarchy structure. Does such a thing even exist?

You will need a parent-child dimension, where each member also keeps the id of its parent; something like

Id; name; parent

1; world;null

2; europe;1

3; north america;1

(...)

123; france; 2

And so on. Whether your particular OLAP solution supports this or not?... that’s a different battle.


which time period covers date string over datetime column My question is a restlessness I been having for a time. So, here it goes:

If I have a datetime column and perform a date search without the time 'yyyy-mm-dd' in a between statement. Which period of time does the query covers?. From this, I can figure some others like timezone related or daylights changes.

So, have you any knowledge or source to check this information?

I know this is not the kind of filter to apply in a datetime column but due a human mistake in a query once I still have doubts

Thank you

A sargable exact alternative to DATE(ColumnName) = DATE'yyyy-mm-dd' is

ColumnName >= TIMESTAMP'yyyy-mm-dd' AND
ColumnName < TIMESTAMP'yyyy-mm-dd' + INTERVAL '1' DAY

Something like

ColumnName >= 'yyyy-mm-dd 00:00:00' AND
ColumnName <= 'yyyy-mm-dd 23:59:59'

is not only more complicated (and bug prone), but also leaves out any values between 'yyyy-mm-dd 23:59:59.000001' and 'yyyy-mm-dd 23:59:59.999999'.


Impact of replication compression & encryption on performance, traffic? What is the impact of MySQL compressed-slave-protocol on replication traffic?
According to High performance MySQL it can lower the traffic to a third of the same replication without the compression. Can you give me something more accurate (more information on this topic)?

What about SSL encryption? It will surely enlarge every single packet, I am not sure how much of an impact it will have on the overall traffic though.

What about performance?

I like this presentation by Giuseppe Bianchi. Starting on page 5 it contains a desctiption of the TLS protocol - segment size, header size, HMAC overhead. As for the handshake, the impact on replication should be negligible. It will only occur on connection, and there may be a key exchange going on every hour, depending on the configuration.
As for the compression, I'd go with what the book says. Or you can try gzipping your binlog file, as the ratio will likely be application-specific. (This may not be accurate if the compression is initialized per packet, not once for the replication stream.)
On performance - type "openssl speed" in the command line, you'll get a benchmark of the throughput of different ciphers on your computer. Find the relevant ones and calculate the utilization given the throughput you expect from the replication stream.


SQL beginner, need help with WHERE syntax error I am having a problem with a sql query that is supposed to find authors that have published at least one book priced $13 or more. IT is supposed to display first and last name, sorted by last name as long as the author has published at least one book priced $13 or more. Below is my query so far:

SELECT authors.au_fname, authors.au_lname, titles.price
FROM authors, titles  ORDER BY au_lname ASC
WHERE titles.price > "13";

The first picture shows the db's first table authors and the second shows titles, which holds the price column.

What am I doing wrong? I cannot figure out what is wrong with my syntax.

Thanks!

Strings are denoted with Single Quotes.

WHERE titles.price > '13'

If your price column is an int/decimal/float (or any numeric) datatype, you don't have to use a string.

WHERE titles.price > 13

Probably better to use the second WHERE, as it is comparing the NUMERIC values, rather than the string values.  Even if the price column IS a string, it will CONVERT the value to a number in order to compare it against the 13.  It doesn't perform as well, but it is more accurate vs. comparing strings.

Also, your WHERE goes before your ORDER BY.

SELECT
FROM
JOIN
WHERE
GROUP BY
HAVING
ORDER BY

Edit: the code below explains the difference between comparing numbers implicitly and comparing numbers as strings.  The commented WHERE clause yields different results due to the fact that it CONVERTS the price (StringNumber) column to a number before it compares it.

WITH CTE_NumberStrings
AS
(SELECT '1' AS StringNumber
 UNION SELECT '2'
 UNION SELECT '3'
 UNION SELECT '12'
 UNION SELECT '23'
 )
 SELECT StringNumber
 FROM CTE_NumberStrings
 WHERE StringNumber > '13'
 --WHERE StringNumber > 13


SQL Server - Do automatically created column statistics get dropped automatically after time? The retired SQL Server 2000 documentation https://www.microsoft.com/en-us/download/details.aspx?id=51958 states:

  "The statistics generated for a column can be deleted if you no longer
  want to retain and maintain them. Statistics created on columns by SQL
  Server (when the AUTO_CREATE_STATISTICS database option is set to ON)
  are aged and dropped automatically."

Obviously this is old documentation related to SQL Server 2000, but I can't find anything official that is more current and says otherwise or that the behaviour is different now.  Can anyone answer definitively?  Thanks..

SQL Server, under no version or circumstance, will automatically remove statistics objects, unless the underlying object (table/view/index) is removed.

Use DROP STATISTICS ... T-SQL or use the SSMS GUI to drop unnecessary statistics, but only do that if you are certain they are not needed.