texts: Adding one record from master-table in addition to other records from detail-table to a MySQL-View

texts:
I am facing a problem creating a MySQL-View. I am not really a pro and i have no clue how to make this work. The situation is the following:
I´ve got a table "Customers" (Master) with all the costumer companies in it and a suitable "Customers_Contacts" (Detail) with all the  company contacts. What i want to do is creating a view with all customer companies and also all of the company contacts.

I am struggling with finding a suitable SQL. I already tried stuff with sub-selects but the result never was the way i wanted it to be.
Here is some (as i hope) explaining material on what my goal is:

Customers

+-----+------------+-----------+------------+------------+-----------------+
| ID  | Matchcode  | FirstName | SecondName | Phone      | Email           |
+-----+------------+-----------+------------+------------+-----------------+
| 123 | Mayer GmbH | NULL      | NULL       | 0170/00000 | zentrale@gmx.de |
+-----+------------+-----------+------------+------------+-----------------+
...

Customers_Contacts
+-----+-------------+-----------+------------+------------+--------------+
| ID  | ID_Customer | FirstName | SecondName | Phone      | Email        |
+-----+-------------+-----------+------------+------------+--------------+
| 111 | 123         | Schmitt   | Else       | 0171/11111 | else@gmx.de  |
| 112 | 123         | Schulz    | Lisa       | 0172/22222 | lisa@gmx.de  |
| 113 | 123         | Marx      | Petra      | 0173/33333 | petra@gmx.de |
+-----+-------------+-----------+------------+------------+--------------+
...

v_Customers_Contacts (View)
+------------+-----------+------------+------------+-----------------+
| Company    | FirstName | SecondName | Phone      | Email           |
+------------+-----------+------------+------------+-----------------+
| Mayer GmbH | NULL      | NULL       | 0170/00000 | zentrale@gmx.de | <-- record from Customers
| Mayer GmbH | Schmitt   | Else       | 0171/11111 | else@gmx.de     |
| Mayer GmbH | Schulz    | Lisa       | 0172/22222 | lisa@gmx.de     |
| Mayer GmbH | Marx      | Petra      | 0173/33333 | petra@gmx.de    |
+------------+-----------+------------+------------+-----------------+
...

I really don't know if this is possible with a MySQL-View so please let me know that first.
I´d be really glad if you could post some SQL-code of your ideas how to make this work.

Thanks and greetings.


texts:
I have a database with a geometry table, each row contains a geometry object, could be a line, a point or a polygon. it also contains the modifyDate of when it was last modified, userID of who last changed it and itemID, a unique id for the item.
in the past, every time an item changes, I just overwrote it. I have recently created a separate table called geometryHistory so a user can view the changes to an item over time (think a polygon representing an invasive weed).
obviously i can currently only go back a couple of weeks, but I have been taking daily dumps (couple of months missing here and there where the backup was silently failing)
What would be the best way for me to 'generate' this history?
My current thinking is to spin up a test server and import all the dumps into new databases based on date eg db_2020-12-01
then creating a new geometry table without a unique key and loading all the tables from the 500+ databases into it then creating some sort of query that will order by itemID and modifyDate then somehow have it group by geometry and itemID with MIN(modifyDate)
In my mind this should work, but as it is going to be a LOT of work (unless i can script it i guess?) I would like to hear others opinions and whether it is the right direction or if there is a better way.
I know there will be some holes, where an item was changed multiple times in a day or where the backups were not running, but in this case, something is better than nothing.


texts: mysql ERROR 1248: Every derived table must have its own alias

texts:
-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `usp_MissingBooks`(p_UsrID BIGINT ,
      p_Product VARCHAR(20))
BEGIN

        DECLARE p_LinkedBoard INT;
        DECLARE p_LinkedSubjects INT;
        DECLARE p_LinkedClasses INT;
        DECLARE p_LinkedBoards INT;
        DECLARE p_SchoolBooks INT;

        SELECT COUNT(*) INTO p_LinkedBoard
        FROM    userboards
        WHERE   userboards.usrid = p_UsrID;
        SELECT COUNT(*) INTO p_LinkedClasses
        FROM    userclasses
        WHERE   userclasses.UsrID = p_UsrID;
        SELECT COUNT(*) INTO p_LinkedSubjects
        FROM    usersubjects
        WHERE   usersubjects.usrid = p_UsrID;
        SELECT COUNT(*) INTO p_LinkedBoards
        FROM    schoolboards;
        SELECT COUNT(*) INTO p_SchoolBooks
        FROM    SchoolBooks;
        Select * From temp_MatchList;

        CREATE TEMPORARY TABLE temp_MatchList(ID BIGINT ,
            Year INT ,
            Name NVARCHAR(100) ,
            SyllabusDisplayName NVARCHAR(100) ,
            BookType NVARCHAR(20) ,
            ClassName NVARCHAR(20) ,
            ClassID INT ,
            ClassNumber INT ,
            BookName NVARCHAR(100) ,
            Board NVARCHAR(100) ,
            Publisher NVARCHAR(100) ,
            SubjectName NVARCHAR(100) ,
            SubjectID INT ,
            BoardID INT ,
            STATUS CHAR(1) ,
            BoardStatus CHAR(1)
        );

    INSERT INTO temp_MatchList
     SELECT * FROM
     (
        SELECT vBooksDetails.*
        FROM
        (
           SELECT DISTINCT * FROM
           (
              SELECT
                classmaster.ClassID AS ClassID,
                CDisplayName,
                subjectmaster.id ,
                SubjectID,
                subjectmaster.DispName ,
                BookName
              FROM classmaster
              JOIN subjectclasslinkage ON
                 classmaster.ClassID = subjectclasslinkage.ClassNumber
             JOIN subjectmaster ON
                 subjectclasslinkage.SubjectId = subjectmaster.id
             LEFT JOIN
              (
                 SELECT * FROM vBooksDetails
                 WHERE
                 (
                   (
                     (p_LinkedBoard = 0 ) OR (BoardID IN (SELECT boardname
    FROM userboards WHERE userboards.usrid = p_UsrID )))
    AND ((p_LinkedBoards = 0 ) OR (BoardID IN (SELECT BoardID FROM schoolboards )))
    AND EXISTS (SELECT * FROM vBoardInfo WHERE ID = BoardID )AND vBooksDetails.Status = 'A'))
    lessonsyllabus ON subjectmaster.id = lessonsyllabus.SubjectID AND
    lessonsyllabus.ClassID = classmaster.ClassID ) TAB WHERE TAB.BookName IS NULL ) TAB_MISSING
    JOIN vBooksDetails ON TAB_MISSING.ClassID = vBooksDetails.ClassID
    AND TAB_MISSING.SubjectID = vBooksDetails.SubjectID AND vBooksDetails.BoardID = 3
    WHERE   vBooksDetails.Status = 'A' AND ((p_LinkedClasses = 0 )
    OR ( vBooksDetails.ClassID IN(SELECT classid FROM userclasses WHERE UsrID = p_UsrID )))
    AND (( p_LinkedSubjects = 0 ) OR (vBooksDetails.SubjectID IN (SELECT subjectid FROM usersubjects
    WHERE usrid = p_UsrID )))
        -- AND ((@LinkedBoard = 0) OR (BoardID IN (SELECT boardname FROM userboards WHERE usrid=@UsrID)) )
    AND ((p_SchoolBooks = 0 ) OR ( vBooksDetails.Id NOT IN ( SELECT BookID FROM SchoolBooks))));

END


texts: Collect data with central management servers and keep them in local table

texts:
I have 50 servers. I manage them from a central management server on SQL Server 2014 Enterprise Edition. I don't use repository.

I could open a query and get result for each one at the same time. But I want to do it in an automated way, like a job, to collect data from each server and store/handle it locally.

Is this possible and if so, how?


texts: Is there a generic way to determine if a row can be deleted, i.e. doesn't have child rows?

texts:
Is there a generic way to determine whether a row in table parent has rows in "child" tables that are referencing it through their foreign keys? I want to be able to tell ahead of time if it can be deleted, i.e. I don't want to try to delete and then catch the exception if it fails.

E.g.:

CREATE TABLE parent (
  id int unsigned primary key
);
CREATE TABLE child_1 (
  id int unsigned primary key,
  parent_id int unsigned,
  CONSTRAINT child_fk1 FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE RESTRICT
);
...
CREATE TABLE child_n (...);

INSERT INTO parent(id) VALUES (1);
INSERT INTO child_1(id, parent_id) VALUES (1, 1);
DELETE FROM parent WHERE id = 1;

In this case we get:

  ERROR 1451 (23000): Cannot delete or update a parent row: a foreign
  key constraint fails (mydb.child_1, CONSTRAINT child_fk1 FOREIGN
  KEY (parent_id) REFERENCES parent (id))

To be clear:

I don't know what the child tables are called.
I don't want to disable foreign key checks and delete the row regardless.
I don't want to do a cascading delete.
I don't want to "soft-delete" the row by adding a deleted boolean column and set this to "true".
I want to find out if it's possible to delete this one row without deleting the children.

I'm thinking I can run a query I against the information_schema to find what the referencing child tables are. I guess that could be a recursive CTE? And then interrogate these tables in turn to find out whether they reference the particular row in the parent table that I want to delete. And potentially that can all be wrapped up into a nice stored procedure. Will that work?

Or is there some other/better way to do this?


texts: How to identify who made changes in SQL server management?

texts: I'm working on the server using SQL Server Management Studio 2005. Is it possible to identify the person who made changes in the SQL server Management Studio 2005 tables data?


texts: How to start and then stop one cyclic process

texts:
I have to start cyclic process in a stored procedure and then have to stop it after some time (after 12-24 hours). Stored procedure's work should not be started twice. SQL Server client (caller) should not wait and should not get execution timeout. How it can be implemented?

CREATE PROC dbo.CyclingProcess
AS
BEGIN

    /*

    .. how to start or stop process by external caller

    */

    WHILE(1 = 1)
    BEGIN

        EXEC mysp_WorkingProcedure -- some working process...
              @input1 = @input1
              ....
             ,@delay  = @delay output

           WAITFOR DELAY @delay
    END

END

Clarification:

I have to do some repeated work during long time. I have to start this process and stop it. Why stored procedure? I have to change some input parameters for [some work] and call it again after some delay. Maybe you know other more smart way to implement it. This cyclic process should work in database.


texts: Why are graph databases bad at CRUD operations?

texts:
I heard that graph DBs are inherently worse at CRUD operations than relational DBs. Is this true? If yes, why is that?

Some thoughts:

To do a crud operation in a realtional DB you basically have to work with one row (best case). This should be quite efficient as long as it is not a columnoriented format (OLAP).

To the best of my knowledge there are 2 popular ways to store a graph:

Either actually storing "objects which point at each other". This would be a natural representation of a graph.

Or storing a table, which lists relations. This would be the non-natural representation of a graph.

In the second case I understand why crud operations are slow: You might have to touch a lot of tables to represent what would just be an update of a single row in a relational schema.

Why is it slow in the first case? To an update or delete you just might have to create/read/update/delete a single object.


texts: Nested queries or temp (#) tables in SQL Server

texts:
I have written a web application which aggregates and presents data from a SQL Server. The data involved is large, and there is a lot of aggregation involved. Currently, under advice from an 'expert' I have used heavily nested queries including the aggregations, however I have just been reading here about optimising large complex queries by breaking them down into temp (#) tables.  This seems to conflict with a lot of literature suggesting that using temp tables is not the best practice.

The web app itself is to be used by quite a few people, so ideally I wouldn't want the temp table, if they need indexing, to conflict with each other.

What is the best way forward here for query optimisation, and best practice for a widely used web app?

Thanks in advance.

CM


texts: What happened to my-*.cnf in MySQL 5.6.8?

texts:
Up to MySQL 5.6.7, the official RPM was shipping with these files:

/usr/share/mysql/my-innodb-heavy-4G.cnf
/usr/share/mysql/my-medium.cnf
/usr/share/mysql/my-huge.cnf
/usr/share/mysql/my-large.cnf
/usr/share/mysql/my-small.cnf

In 5.6.8, these files are gone, and the only one left is:

/usr/share/mysql/my-default.cnf

I'm not a MySQL tuning expert, and was used to always start with my-innodb-heavy-4G.cnf.
Now I am left with a single configuration option:

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

Is tuning this only variable the magic key to most optimizations, or are there other relevant parameters that I should tune when installing a new server?

In which case, where can I find an equivalent file, that would contain sensible defaults for a InnoDB-only, 4GB RAM dedicated MySQL server?


texts: Fetching index avg fragmentation for each database

texts:
i am  beginner and writing a query  to fetch avg fragmentation in percent to rebuild indexex for db i used cursor but  i m able to bring for one database so how to include for all database using outer cursor excluding master database ... please help me to put outer cursor to bring for all database exlcuding  master

my query is  like this

 DECLARE @TableName VARCHAR(255)
DECLARE @schema VARCHAR(50)
DECLARE @indexName VARCHAR(255)
Declare @avgfrag int
DECLARE @sql NVARCHAR(500)

DECLARE TableCursor CURSOR FOR

SELECT schema_name(t.schema_id)                           AS [Schema],
       object_name(ps.object_id)                          AS [Table],
       i.name                                             AS [Index],
       --ps.Index_type_desc                                 AS IndexType,
       convert(TINYINT,ps.avg_fragmentation_in_percent)   AS [AvgFrag%]
       --convert(TINYINT,ps.avg_page_space_used_in_percent) AS [AvgSpaceUsed%],
       --ps.record_count                                    AS RecordCnt,
       --ps.fragment_count                                  AS FragmentCnt
FROM     sys.dm_db_index_physical_stats(db_id(db_name()),NULL,NULL,NULL,'DETAILED') ps -- Faster option: SAMPLED
         INNER JOIN sys.indexes i
           ON ps.object_id = i.object_id
              AND ps.index_id = i.index_id
         INNER JOIN sys.tables t
           ON ps.object_id = t.object_id

WHERE  ps.avg_fragmentation_in_percent >=1

ORDER BY ps.avg_fragmentation_in_percent desc

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @schema,@TableName,@indexname,@avgfrag
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'ALTER INDEX  '+ @indexname + ' ON ' + @Schema  +'.'+ @tablename + ' REBUILD ' --+' with (online=on)'
print @SQL +'   '+ convert(varchar,@avgfrag)

EXEC (@sql)

FETCH NEXT FROM TableCursor INTO @schema,@TableName,@indexname,@avgfrag
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO


texts:
I was wondering if someone could help to understand this query, basically I'm aware about SLEEP() function but I've never seen this implementation before.
Why there are no any results returned?

mysql> SELECT fname, lname FROM contacts WHERE fname = 'test' / SLEEP(1) / '';
Empty set, 14 warnings (7.00 sec)

Warnings:

Warning | 1292 | Truncated incorrect DOUBLE value: 'test'
...


texts: How to implement a Set based algorithm/UDF

texts:
I have an algorithm that I need to run against every row in a table with 800K rows and 38 columns.
The algorithm is implemented in VBA and does a bunch of math using values from some columns to manipulate other columns.

I'm currently using Excel (ADO) to query SQL and use VBA with client side cursors to apply the algorithm by loop through every row. It works but takes 7 hours to run.

The VBA code is complex enough that it would be a lot of work to recode it into T-SQL.

I've read about CLR integration and UDFs as possible routes. I also thought about putting the VBA code in an SSIS script task to get closer to the database but am sure an expert methodology to this type of performance problem exists.

Ideally I'd be able to run the algorithm against as many rows (all?) as possible in a parallel set based way.

Any help greatly predicated on how to get best performance with this type of problem.

--Edit

Thanks for the comments, I'm using MS SQL 2014 Enterprise, here's some more details:

The algorithm finds characteristic patterns in time series data. The functions within the algorithm perform polynomial smoothing, windowing, and finds regions of interest based on input criteria, returning a dozen values and some Boolean results.

My question is more about methodology than the actual algorithm: If I want to achieve parallel computation on many rows at once, what are my options.

I see re-code into T-SQL is recommended which is a lot of work but possible, however the algorithm developer works in VBA and it changes frequently so I'd need keep in sync with the T-SQL version and re-validate every change.

Is T-SQL the only way to implement set based functions?


texts: Creating a numeric user ID from distinct combinations of IP and user agent from a server log

texts:
I'm trying to create a numeric user ID from the distinct combination of IP addresses (alphanumerically anonymized) and the user agents. The table is a pre-processed server log file and contains among other variables the url, the IP address and the user agent of every html-request (click) the users produce.

First I created a table of distinct combinations of IPs and user agents. Among the 15 million requests, there are 1.5 million distinct combinations. I added an auto-incrementing ID:

CREATE TABLE users (ip_adress varchar(255), user_agent varchar(255));
INSERT INTO users (ip_adress, user_agent)
SELECT ip_adress, user_agent
FROM (SELECT DISTINCT ip_adress, user_agent FROM server_requests) AS internalQuery
order by ip_adress asc;
ALTER TABLE users ADD user_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

Now I need to add the user_id from the table users to the table server_requests. What do you think of this query? There is no error, but the query takes far too long (over 24h, now i stopped it):

SELECT
r.request_id,
r.request_datetime,
u.user_id,
r.url_string,
r.ip_adress,
r.user_agent
FROM server_requests r
LEFT JOIN users u
ON r.ip_adress=u.ip_adress
AND r.user_agent=u.user_agent
ORDER BY r.request_id ASC;

Do you have any idea how to improve the query? Or is it normal, that it takes so much time? (Query example tested with MySQL)

I did this query in MySQL for testing reasons. But I plan to use PostgreSQL for the final query. I'm sure there is an equivalent to AUTO_INCREMENT in PostgreSQL. Because the IP addresses are alphanumerically anonymized, I used VARCHAR. The length of (255) is definitely overkill in this case.


texts: INDEX SKIP SCAN performance on Index where first columns only contain 1 value

texts:
We're running an Oracle 11.2.0.3 database and have a table with above 10 columns in it. There is a non-unique index over all these columns.

For simplicity in this example, lets assume the columns are called "COLUMN1, COLUMN2, etc. and the index is built on these columns in the same order.

The table contains 120 million entries. Every entry has the exact same value "0" for COLUMN1 and COLUMN2. The other columns contain different values.

Introductory Question...
Based on this, should the index basically be structured such that COLUMN 1 and COLUMN2 would only contain single branches each, and then COLUMN3+ contain multiple branches based on their differing values. ie something like this...

     ROOT NODE
         |
        "0"             (for COLUMN 1)
         |
        "0"             (for COLUMN 2)
    /``/`|`\``\
  /`  /  |  \  `\
"1" "2" "3" "4" "5"...  (for COLUMN 3)
        ...             (etc for COLUMN4+)

This kind of structure would seem logical, as COLUMN1 and COLUMN2 only contain the single value for all entries, so it shouldn't require more than a single branch for each of these columns.

Following on from this, and leading on to the main question...

We have a query similar to the following...

SELECT *
  FROM MYTABLE
 WHERE COLUMN3 IN ( "5", "6", "7" )
   AND COLUMN4 IN ( "A", "B", "C" );

As you would expect, the Explain Plan shows that this query uses an INDEX SKIP SCAN on the index, as COLUMN1 and COLUMN2 aren't in the WHERE clause.

To a human, we can see that because COLUMN1 and COLUMN2 have a consistent value, Oracle should potentially be able to execute this query as a normal range scan. However, Oracle doesn't have human insight in this way.

The Main Question...
How different is an INDEX SKIP SCAN going to perform in this example compared to a normal range scan? My assumption is that the processing involved for an INDEX SKIP SCAN is going to be significantly different to the processing for a normal range scan, so the performance would be significantly worse even though the first 2 columns have a consistent value. Is this correct?

Is anyone able to provide some information about the Oracle implementation of the different index lookups. I'm more interested to know whether Oracle is smart enough to effectively ignore the first 2 columns when performing the SKIP SCAN, and thus run relatively efficiently, or whether it is still going to have a lot of overhead because the SKIP SCAN implementation processes the index in a different way to a normal range lookup.

Thanks


texts: SQL Server Installation Error: Object reference not set to an instance of an object

texts:
I am trying to install Microsoft SQL Server, but whenever I try to do so, the Database Engine Services feature fails and I get the following error:

  Object reference not set to an instance of an object

This has happened on Enterprise & Developer editions, both 2016 and 2017. The summary log isn't particularly helpful (at least that I can see), and the only other advice I've found is to make sure .NET Frameworks 3.5 and 4 are enabled on the PC.

Please see here for summary, detail, and what I believe are the error logs. If anything else is needed I'll drop it in that folder as well.

Any help would be greatly appreciated.


texts:
Example consider two tables Persons and Employee

Persons table sample:

Name,Address
1. john,new York
2. Diana,new jersey

Employee table sample:

Employee ID,Team
1. 123,developer
2. 568,tester

Required join Table output should be like

Name,Address,Employee ID,Team
 1.john,new York,null,null
 2.Diana,new jersey,null,null
 3.null.null,123,developer
 4.null,null,568,tester

I want above sample as output is there any way to solve this issue.


texts: Install last CU for SP2 without installing SP1 in sql server

texts:
A client asked me to update their SQL Server.

It's an 2016 base release (13.00.1601.00)

Microsoft already released the SP2 CU10. I know that cumulative updates are... like... cumulative, but even between service packs?

I can jump and install directly SP2 CU10 or do I need to install the last CU for SP1 before?


texts:
I am trying to setup DRBD/MySQL/Corosync/Pacemaker on AWS EC2 instances, though I am not sure if it will work since how to integrate VIP is a problem for me.

Anyone have any thoughts on what to do ?


texts:
When I run a query on my table, I have no clue how long the process will take. I am working with a table with 11 million rows so queries typically run for a few minutes (AWS RDS). Is there a way to get a progress bar or a better sense of how long queries will take? I typically execute a query to 1/10 of the table to get an idea of the time required.

EDIT: I added my specific situation in a notebook

The query I like to run calculates a moving average for several columns. A testing query is copied below (1 column only, only for annual scores, limited output):

SELECT year,month, ptotww_m_30spfaf06, temporal_resolution,
    SUM(ptotww_m_30spfaf06)
        OVER(ORDER BY year ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as ptotwwma_m_30spfaf06
FROM y2018m05d29_rh_total_demand_postgis_30spfaf06_v01_v01
WHERE temporal_resolution = 'year'
LIMIT 200

The sample already takes 52s to run and does not work with the monthly values. The monthly 10 year moving average of Jan 1969 should be the average of jan 1960, jan 1961 ... jan 1969.


texts: Restore System Databases from MDF/LOG files

texts:
We recently experienced a hardware failure that caused our SQL 2005 server to take it in the dump.  The good news is that we have the MDF and LOG files for all the databases, the bad news is that we did not have .bak files for the system databases.

Is there any way to restore the System Databases (msdb, master, model) from the MDF/LOG files, or is there a process I can follow to create a bak file that I can then restore them from?

We have quite a few configuration items that were lost in the recovery.  I have been working on getting the mission critical application stuff back in working order and now I'm trying to restore all of our jobs and such.

Any help is greatly appreciated.


texts: Guidelines on best indexing strategy for varying searches on 20+ columns

texts:
I’m running a search engine for cars. It’s backed by a postgresql 9.3 installation.

Now I’m unsure about the best approach/strategy on doing index optimization for the fronted search.

The problem:

The table containing the cars holds a around 1,5 million rows. People that searches for cars needs different criteria to search by. Some search by brand/model, some by year, some by mileage, some by price and some by special equipment etc. etc. - and often they combine a whole bunch of criteria together. Of cause some, like brand/mode and price, are used more frequently than others. In total we offer: 9 category criteria like brand/model or body type, plus 5 numeric criteria like price or mileage, plus 12 boolean criteria like equipment. Lastly people can order the results by different columns (year, price, mileage and a score we create about the cars). By default we order by our own generated score.

What I’ve done so far:

I have analyzed the usage of the criteria “lightly”, and created a few indexes (10). Among those, are e.g. indexes on price, mileage and a combined index on brand/model. Since we are only interested in showing results for cars which is actually for sale, the indexes are made as partial indexes on a sales state column.

Questions:

How would you go about analyzing and determining what columns should be indexed, and how?
What is the best strategy when optimizing indexes for searches happening on 20 + columns, where the use and the combinations varies a lot? (To just index everything, to index some of the columns, to do combined indexes, to only do single column indexes etc. etc.)
I expect that it does not make sense to index all columns?
I expect it does not make sense to index boolean columns?
Is it better to do a combined index on 5 frequently used columns rather than having individual indexes on each of them?
Would it be a good idea to have all indexes sorted by my default sorting?
Do you have so experiences with other approaches that could greatly improve performance (e.g. forcing indexes to stay in memory etc.)?


texts: why when I change a column from DATETIME to DATE, I can't see any storage improvement?

texts:
Long story short. I have a table with more than 10millions rows with a datetime field. WE can change that to DATE ( because date uses 3bytes, and datetime 8).

I thought if I:

alter table table1 alter column column1 date

I would reclaim more than half the space allocated.

but doing some tests, I can't see any space being reclaimed.

exec sp_spaceused datas2
datas   110000                  8136 KB 8080 KB 8 KB    48 KB --with datetime
datas   110000                  8136 KB 8080 KB 8 KB    48 KB --with date

both with 8080KB.

but then if I create a new table, with the same ammount of data, but with DATE since the beggining, I can see a good storage improvement:

datas2  110000                  4744 KB 4688 KB 8 KB    48 KB

Now my table has 4688 KB.

How can I reclaim that space on a table that would be altered from datetime to date?


texts: Why am I getting Table scan on a partitioned table query?

texts:
I have table Inventory_Break partitioned on IR_ID (int).  I also created a non-clustered index for IR_ID on the table.

When I run the following:

SELECT  *
FROM    dbo.INVENTORY_BREAK
WHERE   IR_ID IN ( 100, 101, 102 )

I get the following execution plan:

It states Table Scan (HEAP).  Not quite sure what it means in the context of a partitioned table.  I also don't see that it uses any kind of index.  And yet, it must, because the query comes back fairly fast (e.g. the table has 6 million rows).

So, my questions are:

What does Table Scan (HEAP) mean for a partitioned table?
Does it indeed use an index, perhaps behind the scenes?
Is there anything I need to do to improve efficiency?


texts: DB2: Why does translate function also replace spaces?

texts:
I would like to remove control characters (HEX \x00 until \x7F) from a DB2 data base field. For this I tried to apply the following translate-function:

TRANSLATE(field, 'X',  x'000102030405060708090A0B0C0D0E0F101112131415161718191A1B1C1D1E1F')

It does work for control chracters, but the function also translates question marks, and I don't understand why? Interestingly question marks are not replaced by "X" like the control characters but by a space \x20.

Why does it behave like that?

Original values:

Text View: mhlkm
Hex: 6D 68 6C 6B 6D 3F
Text View in Hex Viewer: mhlkm?

Translated values:

Text View: mhlkm
Hex: 6D 68 6C 6B 6D 20
Text View in Hex Viewer: mhlkm


texts: SQL Server 2016 compress function details - external decompression

texts: According to SQL Server 2016 docs COMPRESS and DECOMPRESS methods are just a blackbox - you put the data in and after some magic it gets compressed or decompressed. The problem is that I need to find a way on how to decompress this data outside SQL Server - using Snowflake or Azure Data Factory preferably. Does anyone please have a clue on how to approach this topic? This difficult in particular due to lack of detailed docs on the compression method/algorithm used.


texts: How to investigate memory grant warnings?

texts:
Saw this warning from query in plan cache
<Warnings>
     <MemoryGrantWarning GrantWarningKind="Excessive Grant" RequestedMemory="2751552" GrantedMemory="2751552" MaxUsedMemory="768" />
</Warnings>

Does RequestedMemory represent bytes or kilobytes?
How do you troubleshoot what caused this warning?


texts: How to install Slony for 64-bit PostgreSQL 9.3 on Windows?

texts:
I have installed 64-bit version of PostgreSQL 9.3.5 from EnterpriseDB installer. Now I want to install Slony-I through Application Stack Builder, but it offers only 32-bit version of Slony, incompatible with 64-bit PostgreSQL. My OS in Windows 7 64-bit.

Is there any easy way how to install it? Or do I have to build it from source? Or is there no way to do it except for move to 32-bit?


texts:
I need to show the count of all rows grouped by month and then pivot it
Appreciate an easy solution for that

Or alternatively if someone can help me fix this

I've used dynamic pivot query

DECLARE @cols AS NVARCHAR(MAX);
DECLARE @query AS NVARCHAR(MAX);

select @cols = STUFF((SELECT distinct ',' +
                        QUOTENAME(DATENAME(MONTH, CONVERT(DATETIME, createDate)))
                      FROM OSCL
                      WHERE     ([status] = - 3)

                      FOR XML PATH(''), TYPE
                     ).value('.', 'NVARCHAR(MAX)')
                        , 1, 1, '');

SELECT @query =

'SELECT *
FROM
(
  SELECT
    callID,
    DATENAME(MONTH, CONVERT(DATETIME, createDate)) as Months
  FROM OSCL
  WHERE     ([status] = - 3)
) AS t
PIVOT
(
  COUNT(callID)
  FOR Months IN( ' + @cols + ' )' +
' ) AS p ; ';

 execute(@query);

This gives me data like this

April | February | March
44    | 1        | 5

But, I want to show in Sequence

January | February | March | April (Till the current month)
0       | 1        | 5     | 44

Table Example Is This
For Example

callID   |   U_Regn   |   createDate

What I Want To Show Is
A Calendar

             January | February | March | April
Database1 |   0      | 1        | 5     | 44
Database2 |   0      | 6        | 6     | 6


texts:
Hi I am absolute begginer in Oracle domain. I would like to learn way how to clone copy oracle database to som backup, use this backup on another server.

Oracle is installed on Windows server 2003.

I have some question:

When I need copy oracle DB I must shutdown service?
I found many examples but I don’t know which is suitable for beginner.
It exist som graphics Tool for cloning Oracle DB?


texts: DB2 - Not enough storage in the database heap error when trying to create database

texts:
So like the title says I'm trying to create a database using this script

db2 -v -t "CREATE DATABASE %DBNAME% ON '%DBDRIVE%'
USING CODESET UTF-8 TERRITORY US COLLATE
USING SYSTEM CATALOG TABLESPACE MANAGED BY SYSTEM
USING ('%DBDRIVE%%DBDIR%\%DBNAME%\sys') TEMPORARY TABLESPACE MANAGED BY SYSTEM
USING ('%DBDRIVE%%DBDIR%\%DBNAME%\systmp') USER TABLESPACE MANAGED BY SYSTEM
USING ('%DBDRIVE%%DBDIR%\%DBNAME%\usr')";

but I get an error that says:

  SQL0956C Not enough storage is available in the database heap to process the statement. SQLSTATE=57011

I've looked online about this error and it says to use this command to increase the database heap

db2 UPDATE DB CFG FOR <db-name>
  USING DBHEAP <value>

I've set every database's dbheap value to automatic. I've also checked the values in the dbm such as: database monitor heap size (4KB) -> AUTOMATIC(66) and Size of instance shared memory (4KB) -> AUTOMATIC(1000000). So everything is set to automatic but there is still "not enough memory". also I looked in the db2diag.log file and this seems to be the main error:

2014-02-12-09.09.38.562000-480 E36299119H2188       LEVEL: Error (OS)
PID     : 2540                 TID : 7140           PROC : db2syscs.exe
INSTANCE: DB2                  NODE : 000           DB   : COM1_513
APPHDL  : 0-11886              APPID: *LOCAL.DB2.140212164245
AUTHID  : ADMINISTRATOR        HOSTNAME: CM-RM-WIN04
EDUID   : 7140                 EDUNAME: db2agent (idle) 0
FUNCTION: DB2 UDB, SQO Memory Management, sqloLogMemoryCondition, probe:100
CALLED  : OS, -, VirtualAlloc
OSERR   : 8 "Not enough storage is available to process this command."
MESSAGE : Private memory and/or virtual address space exhausted
DATA #1 : Requested size, PD_TYPE_MEM_REQUESTED_SIZE, 4 bytes
1048576
DATA #2 : Current set size, PD_TYPE_SET_SIZE, 4 bytes
65536

This seems to be saying there is not enough memory on the machine, which is weird because when I see the PF Usage in Windows Task Manager only about 3 / 12 GB of RAM is in use...

I am using DB2 v10.1 and Windows 2003 Server 32 bit

Also I believe this problem is due to the memory management of the databases I've made on this machine. Not positive though.


texts:
The SQL Server Management Studio is working just fine when we RDP into the server and try to perform any sa privileges.However, when we use it from our local machines it is throwing the errors

Situation:

SSMS 2016 client

Version: Microsoft SQL Server 2014- Service Pack 1 -Standard Edition

TCP/IP is also enabled.

I can connect successfully to the database server using management studio but when I try to open database diagrams, I get the below error. I don't even see SQL server agent even if we login with sa privileges.

SQL Server agent service is running.

  TITLE: Microsoft SQL Server Management Studio
  ------------------------------
   An exception occurred while executing a Transact-SQL statement or batch.(Microsoft.SqlServer.ConnectionInfo)
   ------------------------------
   ADDITIONAL INFORMATION:
   A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.) (Microsoft SQL Server, Error: 10054)
   For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=10054&LinkId=20476
  ------------------------------
  An existing connection was forcibly closed by the remote host
  ------------------------------
  BUTTONS:
  OK


texts: How to query multiple tables to see if they share a common column?

texts:
Say I have 100 tables without a uniform naming system. I.e. table 1 is named "tbl_Budget", table 2 is "tbl_Price", etc. I need an efficient way to query each of them to determine if they have a particular column name, say column A, then need the output to look something like this:

TABLE NAME | HAS COLUMN
tbl_Budget   TRUE
tbl_Price    FALSE

In other words, I need to do something like this, but for multiple tables: https://stackoverflow.com/questions/3395798/mysql-check-if-a-column-exists-in-a-table-with-sql


texts: What will happen with cached index or table when it is modified, will it stay in cache?

texts: if PostgreSQL loads something (for example an index) into cache, what happens if it (cached index) gets modified? Is it going to be removed from cache or not?


texts: I have in the SQL server a lot of databases, but one of them has been dropped, so I need to create a trigger to send me an email when someone try to drop another database. The e-mail should contain the user name and the the name of the database.


texts: Understanding varchar(max) 8000 column and why I can store more than 8000 characters in it

texts:
From this Microsoft doc,+

  n defines the string length and can be a value from 1 through 8,000.
  max indicates that the maximum storage size is 2^31-1 bytes (2 GB).
  The storage size is the actual length of the data entered + 2 bytes.

Please help me understand this.

The max characters for varchar seems to be 8000, which is way less than 2GB worth of data.

I see that there are records in this varchar(max) column of a specific table that have len(mycolumn) > 100 000.
Thus I know I can get way more than 8000 characters into a varchar(max) column.

Question 1: How does the 8000 characters come into play and where should I be aware of it?

Question 2 : will a .net datareader query to this column always return the full result with 100 000+ character?


texts: SQL Server: does ddladmin rights contain datareader and datawriter rights to the database?

texts: Does db_ddladmin fixed database role include db_datareader and db_datawriter rights, so that you don't have to grant those rights explicitly?


texts:
I'm trying to dump a MySQL (5.7) database of a about 150 GB, out of which most data (about 90 GB) is in a single table. I tried using MySQL Workbench (6.3) to do this through the GUI. The process starts, and most tables finish, but the big ones eventually fail.

I got recommended to use the commandline tool mysqldump.exe instead, and I tried that. This seems to work, but also eventually fails on the big tables, and reports:

mysqldump: Got Errno 28 on write

I'm trying to dump to an external drive with plenty of space.

One curious thing, is the failure always seems to happen exactly when the file in question hits 4 GB (or 3,99GB). I've tried looking around for this, but no luck!

I'm running Windows 10, 64-bit, mysqldump is 64-bit, I have 16 GB of RAM in the system. I don't feel like I should hit any 4 GB limits, but I'm probably missing something very basic?

Hope you guys can help me out!

The command in running to do the dump looks like this:

mysqldump -t -u root -p [database] [table] > J:\table_name.sql


texts: Question regarding 2NF partial key dependency

texts:
If the functional depencies are
   AB-->CD
   BC-->D
   IS the relation still in 2NF?,I mean since AB is the key and the 2nd BC,out of which B is part of the key,is the relation still in 2NF??


texts: What are the parameters to install oracle 11g r2 command line?

texts:
I'm trying to install oracle 11g R2 using a responsefile. I do like this:

./runInstaller -responseFile /home/oracle/database/response/db_install.rsp

But when I execute the command, it always open my Windows X (I'm using moba Extern). How can I install Oracle 11G R2 in command line?

Note: I want to install software only!


texts: Can't grant privileges to "root" when it has no password in MariaDB

texts:
I am setting up a small development local server (is a VM running CentOS 7) and because of that I didn't add any password to MySQL after run the command: mysql_secure_installation. Now I need to connect from the host through MySQL Administrator but I can't because the user root isn't allowed to connect from the host IP address so I am gonna to change that by giving root all privileges and the host.

This is what I have done so far:

$ mysql -uroot -p
Enter password:  [here is just ENTER because root hasn't a password]
Server version: 10.1.18-MariaDB MariaDB Server

MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.3.%' WITH GRANT OPTION;
ERROR 1133 (28000): Can't find any matching row in the user table

I have found that error so I do some research on Google and found a bunch of links so I pick some of them like this one and tried:

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.3.%' WITH GRANT OPTION;
ERROR 1133 (28000): Can't find any matching row in the user table

But still not working, is a restriction and I am not aware? is a configuration on the my.cnf file? is a problem in the SQL query? Any help?


texts: SQL Agent running Ola scripts: all fine and perfect but a differential backup on a specific database fails

texts:
I've two SQL servers with multiple DBs. On both, I've set up maintenance tasks using the Ola procedures. One DB server is Enterprise SQL with Agent and the other is Express using Win scheduler to run procedures.

All database backups are done towards a network share (QNAP NAS) with plenty of space (800Gb with 750GB still free). ALL of the backups, of system DBS and user DBS  works perfectly but, a differential backup of the main user DB is always failing with I/O error,

  Message 3271, gravity 16, error 112 Not enough space on disk

which obviously is a false error reason.  I'm running FULL backups at 6:00 and 20:00 with DIFF backups at lunch break (13:00). In between LOG backups every hour.  Please note that ALL of the other backups run, even the DIFF backups on smaller user DBs, the only one failing is always on the same DB (which is the biggest, most important and active).

All checks on DB give no errors.
Networks shares works.
Surfing forum I noticed that others are experiencing such I/O error
erratically.
SQL Server version is 13.0.5101.9

Any ideas?


texts: is it possible to use AAG with two 2-Node Clusters across two datacenters?

texts:
Is the following scenario possible?

2-Node Automatic Failover Cluster in DC1, shared disk
2-Node Automatic Failover Cluster in DC2, shared disk
employ a 'Node and Disk Majority'
employ AAG across datacenters
failover will be manual; availability mode asynchronous

The datacenters are up to 15.5-miles apart.

Thanks for the help!


texts:
I'm using DB2 V7 and I have a two table PARTLIST and PARTTYPE as follow :

CREATE TABLE PARTLIST (PART VARCHAR(16),
                      SUBPART VARCHAR(16),
                      QUANTITY INT,
                      Line_number INT);

CREATE TABLE PARTTYPE (PART VARCHAR(16),
                      IS_ASSEMBLY INT);

INSERT INTO PARTLIST VALUES
('PRODUCT1','PART1'    ,1        ,1),
('PRODUCT1','PART2'    ,1        ,2),
('PRODUCT1','SUBASSEMBLY1' ,2        ,3),
('PRODUCT1','PRODUCT2' ,1        ,4),
('SUBASSEMBLY1','PART3' ,4        ,1),
('SUBASSEMBLY1','PART4'    ,4        ,2),
('PRODUCT2','PART3'    ,2        ,2),
('PRODUCT9','PART6'    ,5        ,1);

INSERT INTO PARTTYPE VALUES
('SUBASSEMBLY1',1);

I'm using this Query to browse the entire hierarchy of the PARTLIST table :

WITH RPL (PART, SUBPART, QUANTITY) AS
           (
              SELECT ROOT.PART, ROOT.SUBPART, ROOT.QUANTITY
                 FROM PARTLIST ROOT
             UNION ALL
              SELECT PARENT.PART, CHILD.SUBPART,
                     PARENT.QUANTITY*CHILD.QUANTITY
                 FROM RPL PARENT, PARTLIST CHILD
                 WHERE PARENT.SUBPART = CHILD.PART
           )
SELECT PART, SUBPART, QUANTITY AS "QTY"
   FROM RPL
   ORDER BY PART, SUBPART;

I need some help to only "explode" part that are an assembly (Based on PARTTYPE Table) and to add a column preserving the order of the line in th result.
The desired result should be something like this :

PART         SUBPART         QTY    LINE NUMBER
-----------------------------------------------
PRODUCT1     PART1           1      1
PRODUCT1     PART2           1      2
PRODUCT1     SUBASSEMBLY1    2      3
PRODUCT1     PART3           2      4
PRODUCT2     PART3           2      1
PRODUCT9     PART6           5      1
SUBASSEMBLY1 PART3           4      1
SUBASSEMBLY1 PART4           4      2

fiddle


texts: Query processor ran out of internal resources and could not produce a query plan” - MariaDB


texts: Getting ORA-01017: invalid username/password in non-CDB database

texts:
I have installed Oracle 12c (software only in Linux) and created a non-CDB database (single tenant) database. Following is my listener.ora.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ora12cl)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
SID_LIST_LISTENER=
(SID_LIST=
  (SID_DESC=
    (SID_NAME=orcl)
    (GLOBAL_DBNAME=orcl)
  )
)

This is my tnsnames.ora

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ora12cl)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = orcl)
    )
  )

Here is my initorcl.ora

db_name=orcl
db_block_size=8192
memory_target=300M
memory_max_target=300M
processes=200
job_queue_processes=10
open_cursors=500
fast_start_mttr_target=500
undo_management=AUTO
undo_tablespace=UNDOTBS1

I am able to startup and mount the database successfully. I can connect to it

 [oracle@ora12cl dbs]$ sqlplus sys/***** as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue May 10 23:53:18 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>

But if I try to connect using the instance (as I should from a remote system such as my Windows host):

[oracle@ora12cl dbs]$ sqlplus sys/dolan123 as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue May 10 23:53:18 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@ora12cl dbs]$ sqlplus sys/******@orcl as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue May 10 23:54:47 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied

I am getting ORA-01017.

I see no reason why should not able to login using the instance name. Note it's a non-CDB database.

[oracle@ora12cl dbs]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue May 10 23:56:43 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select name, cdb from v$database;

NAME      CDB
--------- ---
ORCL      NO

Can anyone help on this please?


texts: How to pivot a table skipping bad value in Postgres

texts:
Given a table like:

 label |   x   |   y   |   z
-------+-------+-------+------
 AAA   | NULL  |   7   |   9
 BBB   | 0     |  10   |   0
 CCC   | 12    | NULL  |   0

create table coords (label text, x integer, y integer, z integer);
insert into coords(label,x,y,z)
values
    ('AAA',NULL,7,9),
    ('BBB',0,10,0),
    ('CCC',12,NULL,0);

I need to insert in another table values by label when they are not NULL or 0s.
So the final result will be:

label | coord | value |
------+-------+-------+
AAA   |   y   |  7    |
AAA   |   z   |  9    |
BBB   |   y   |  10   |
CCC   |   x   |  12   |

I tried with different OVER/PARTITION but probably I am missing something. Any help?


texts: Disabled SQL Server Endpoint - now I cannot login

texts:
I have SQL Server 2005 on my local machine.  During the studies of SQL Server network configuration, I logged into my SQL Server Instance and ran the following script:

USE Master;
GO

ALTER ENDPOINT [TSQL Default TCP]
STATE=STOPPED;

After that I am unable to login.  I can't even login using Windows authentication.

How can I revert the change above?  Is there any another way to fix this situation?


texts: Group by with clause in postgresql taking lots of time

texts:
I am new to sql and trying to write a query in postgresql which is taking lot of time during  execution (~8 mins), total number of data in the table is around 298650 rows. and i am using below stated sql query to create an aggregated data which needs to be injected to target, could you please help me to fine tune the query or provide some tips and trick to achieve the same.
Table details
CREATE TABLE test.emp_txn_data (
    date character(1024),
    corp character(1024),
    loc character(1024),
    majoraccount character(1024),
    dept character(1024),
    grosswages character(1024),
    hours character(1024),
    emptype character(1024),
    overtimehourscode character(1024)
);

Data Details:
"date","corp","loc","majoraccount","dept","grosswages","hours","emptype","overtimehourscode"
"2020-08-30","001","00617","61110","10","12.50","1.25","PT","1"
"2020-08-30","001","01371","61110","10","40.00","4.00","PT","2"
"2020-08-30","001","01371","61110","10","40.00","4.00","FT","1"
"2020-08-30","001","01371","61110","10","40.00","4.00","FT","3"
"2020-08-30","001","01371","61110","10","39.50","3.95","PT","2"

SQL query used
WITH main AS (select LOC,majoraccount ,dept, "date"
from api_temp_objects.emp_txn_data  group by loc, dept ,majoraccount, "date"
     ),
 TotalGrosWage AS (select "date" ,LOC,majoraccount ,Dept,
sum(CAST (grosswages as double precision )) as total_gros_wage,
sum(CAST (hours as double precision )) as total_hours
from api_temp_objects.emp_txn_data as A group by loc, dept ,majoraccount,"date" , overtimehourscode having overtimehourscode  = '1')
,
PTTotalHours as (select "date" ,LOC,majoraccount ,Dept,
sum(CAST (hours as double precision )) as p_t_totalHours
from api_temp_objects.emp_txn_data  group by loc, dept ,majoraccount, emptype,"date" ,overtimehourscode having overtimehourscode  = '2'
and emptype ='PT')
,
FTOverTimeHours As
(select "date",LOC,majoraccount ,Dept,
sum(CAST (hours as double precision )) as f_t_overTimeHours
from api_temp_objects.emp_txn_data  group by loc, dept ,majoraccount, emptype,"date" ,overtimehourscode, "date"  having overtimehourscode  = '3'
and emptype ='FT')
,
PTOverTimeHours AS
(select "date" ,LOC,majoraccount ,Dept,
sum(CAST (hours as double precision )) as p_t_overTimeHours
from api_temp_objects.emp_txn_data  group by loc, dept ,majoraccount, emptype,"date" ,overtimehourscode having overtimehourscode  = '3'
and emptype ='PT')
select a.LOC,a.majoraccount ,a.Dept, a."date",b.total_gros_wage, b.total_hours, c.p_t_totalHours, d.f_t_overTimeHours, e.p_t_overTimeHours
from
main a left outer join TotalGrosWage b on a.loc = b.loc and a.majoraccount = b.majoraccount and a.dept = b.dept
left outer join PTTotalHours c on a.loc = c.loc and a.majoraccount = c.majoraccount and a.dept = c.dept
left outer join FTOverTimeHours d on a.loc = d.loc and a.majoraccount = d.majoraccount and a.dept = d.dept
left outer join PTOverTimeHours e on a.loc = e.loc and a.majoraccount = e.majoraccount and a.dept = e.dept
where b.total_gros_wage is not null;


texts: Leap second in Database system (Postgresql and SQL Server)

texts:
This night will be longer because of leap second: https://en.wikipedia.org/wiki/Leap_second

My impact will be minimal in my case, I don't even know if I will recive some weird data like 20150630 23:59:60. But I have monitoring systems from both windows, linux and other systems. In worst case, I will lose one second of data.

I tested conversion of this weird data in postgresql and SQL Server. These are my results:

This select in Postgresql:

SELECT '20150630 23:59:60.001 UTC'::timestamptz;

Is returning: 2015-07-01 02:00:00.001+02
I have the same result with:

SELECT '20150701 00:00:00.001 UTC'::timestamptz;

I think this can be dangerous in critical systems, because, if this data is handled by clients, data can be stored in the wrong order, adding a second to a legitimate data.

In sql server if I try:

SELECT CAST('20150630 23:59:60' AS datetime)

It give me a conversion error. So it is not possible, in sql server, to store this data.

One gives me an error, the other just add a second. I don't like both, because it is impossible to store events in the leap second.

Both are recent systems. SQL server is 2014 on a window 2012 machine. Postgresql is 9.3 on redhat machine (not so update, I think a 6.2).

What if someone ask me to store this data?
I read this and some workarounds: https://stackoverflow.com/questions/19751115/leap-second-handling-in-database

Has anyone faced this as a problem? I mean, this is just a theoretical question in my case, not a real problem (so don't waste time if you don't like theoretical questions)

EDIT:
Tested in Oracle 11.2, error is returned:

    ORA-01852: seconds must be between 0 and 59
    01852. 00000 -  "seconds must be between 0 and 59"


texts: Retrieve Subscription Data on subscriber

texts: I would like to retrieve the subscription data from a SQL server subscriber. The data that I am looking for is the same data that is displayed under the Local Subscriptions folder under Replication folder in the Object Explorer. The trick is that I only want to see the data (ie subscriptions) that is listed in the folder in object explorer and nothing else. I have poked around in the merge replication tables on the databases on the subscriber and although I can retrieve the subscription information from there, there is invalid data among the masses which I think is due to replication cleanups that was not done properly in the past. There must be a way that SQL server retreives the data in the object explorer and displays it but how? and from where? The reason for this is to compare these subscriptions with the publications and determine any orphaned subscriptions. Any help will be appreciated.


texts: Is it possible to restore a database snapshot from a 64 bit system to a 32 bit one?

texts: I would like to perform a restoration test on a snapshot backup of my MariaDB database using a test server running on a 32-bit OS. My production server is a 64-bit system. I would like to know if the architecture of the OS makes a difference to the database files. Both servers are running the same version of MariaDB.


texts: MERGE with IDENTITY_INSERT ON does not work if Identity is not the primary key

texts:
I frequently use MERGE statements and am quite familiar with it. Now I ran into a situation where some tables have IDENTITY columns that are not the primary key. In this situation the script failed although the presence of identity columns was checked in the generation script for the merge statement and identity_insert explicitly was turned on before the merge. However it still fails.

I created a smaller sample for demo that fails as well complaining about the IDENTITY Column:

  Cannot update identity column 'aid'.

I expect since I turned Identity_Insert ON that I can INSERT or UPDATE the value for the IDENTITY column however I like. But it does not work.

Here is the sample code:

CREATE TABLE [dbo].[tm2]
(
    [id] [int] NOT NULL,
    [aid] [int] IDENTITY(1,1) NOT NULL,
    [txt] [nchar](10) NULL,

    CONSTRAINT [PK_tm2]
       PRIMARY KEY CLUSTERED ([id] ASC)
            WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET IDENTITY_INSERT [dbo].tm2 ON

MERGE INTO [dbo].tm2 AS Target
USING (VALUES
  (1,2,'qdqewqf'),
  (2,3,'#ED7F00')
) AS Source ([ID], [aid], [txt]) ON (Target.[ID] = Source.[ID])

WHEN MATCHED AND (Target.aid <> Source.aid OR Target.txt <> Source.txt ) THEN
    UPDATE
    SET
       aid = Source.aid,
       txt = Source.txt

WHEN NOT MATCHED BY TARGET THEN
    INSERT([ID], aid, txt)
    VALUES(Source.[ID], Source.aid, Source.txt)

WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

SET IDENTITY_INSERT [dbo].tm2 OFF

Technical details:

SQL Server 2008 R2
Collation SQL_Latin1_General_CP1_CI_AS


texts: cross database trigger: server principal is not able to access the database under the current security context

texts:
I have a two databases Source and Target hosted in the same SQL Server 2008 R2 instance. I have a Login which is mapped to Users in both databases. Each mapped User has a Role in its respective database which grants access to all objects in that database.

I have a cross-database trigger which propagates updates from a table in Source to a table in Target.

When I connect to the server in SQL Server Managment Studio with the Login and run an update query on Source, the cross-database update succeeds.

But when an equivalent update is initiated using the same Login from within an application, the trigger fails with error:

The server principal 'name' is not able to access the database 'Target' under the current security context.

By running a trace with SQL Server Profiler, I can verify that the update is being run under the same LoginName in both cases.

Questions:

What is special about SSMS that allows the cross-database trigger to work?
What does "current security context" mean?
What do I need to modify to allow the update+trigger to work in the Application's context?

I've read about object ownership chaining, and I've not fully investigated that yet. But since it works in SSMS, I'm tempted to believe that a broken ownership chain is not the issue. But I could be wrong about that! I'd be really grateful for any diagnosis suggestions.

Update: I'm attempting to implement srutzky's suggestion. This is what I have run so far.

use [SourceDB];

create certificate [Access_TargetDB]
   encryption by password = 'password123'
      with subject = 'Cross-DB Access to TargetDB',
      expiry_date = '2099-12-31';

add signature to [MyTrigger]
    by certificate [Access_TargetDB]
    with password = 'password123';

backup certificate [Access_TargetDB]
    to file = 'C:\Access_TargetDB.cert';

use [TargetDB];

create certificate [SourceDB]
    from file = 'C:\Access_TargetDB.cert';

create user [SourceDBUser] for certificate [SourceDB];

exec sp_addrolemember 'StandardUserRole', 'SourceDBUser';

I am still getting the same error when attempting to update the table in SourceDB with the Application Role context.

I'm attempting to implement srutzky's 2nd suggestion. In addition to the above, I have also run the following. But the suggestion assumes a target stored procedure which I do not have, so maybe this isn't actually a valid test.

use master;

create certificate [SourceDB]
    from file = 'C:\SourceDB.cert';

create login [SourceDBLogin] from certificate [SourceDB];

grant authenticate server to [SourceDBLogin];

Still no change in the permissions available in the Application Role context.


texts: Optimizing column types for reduced overall database size

texts:
Given the following table:

CREATE TABLE avl_historico (
    fecha timestamp with time zone NOT NULL,
    latitud double precision DEFAULT 0 NOT NULL,
    longitud double precision DEFAULT 0 NOT NULL,
    altitud double precision DEFAULT 0 NOT NULL,
    velocidad double precision DEFAULT 0 NOT NULL,
    cog double precision DEFAULT 0 NOT NULL,
    nsat integer DEFAULT 0 NOT NULL,
    tipo character(1),
    utc_hora time without time zone,
    fix_fecha date,
    imei bigint NOT NULL,
    registro timestamp with time zone,
    input1 integer DEFAULT 0,
    input2 integer DEFAULT 0,
    input3 integer DEFAULT 0,
    input4 integer DEFAULT 0,
    hdop double precision,
    adc double precision DEFAULT (-99),
    ignicion integer DEFAULT 1,
    adc2 double precision,
    power integer,
    driverid integer,
    ibutton2 integer,
    ibutton3 integer,
    ibutton4 integer,
    trailerid integer,
    adc3 double precision,
    adc4 double precision,
    horometro bigint,
    odometro bigint,
    panico integer DEFAULT 0,
    bateria double precision,
    bateriaint double precision
);

Ref: 240 bytes / row using pg_column_size()

With about 15K tables (Over 1TB total used space) that inherit from this table using "partitioning" based on the imei column as in following example:

CREATE TABLE avl_historico_201424 (
    CONSTRAINT reports_avl_historico_only_201424 CHECK ((imei = 201424))
)
INHERITS (avl_historico);

And, considering that several column types are "bigger" that what they are actually used for. For example: column tipo could be changed into boolean and, several integer columns into smallint.

Would altering some column types actually reduce overall database size?

Specs:

PostgreSQL 9.4.9 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit
Debian GNU/Linux 8.6 (jessie)
/dbdata (ext4): 2.1TB used out of 2.9TB
Sector size (logical/physical): 512 bytes / 512 bytes

Should I provide any additional info for better understanding of the underlying structure and/or data?

Reference for numeric types: https://www.postgresql.org/docs/9.4/static/datatype-numeric.html


texts: Creating a table with Rank ordering in Oracle

texts:
I am trying to create a table in Oracle as follow:
create table hops.t_distribution AS
(select
UID,
CONTACT_EMAIL,
PRODUCT_ID,
rank() over(Partition by UID order by to_number(PRODUCT_ID)) as PRIORITY_ORDER
from hops.staging_test);

hops.staging_test is basically the same table without the PRIORITY_ORDER column.
However, I am getting an ORA error because you can't order by in a create table statement. Is there another clever way to do this?
Thank you!


texts: PostgreSQL Give idetifier for transaction?

texts:
Is there a way to give a identifier for Postgresql transaction.
Ex :

 begin transaction a;
 ...

If not, will this be supported in future.

I'm asking this if transaction has identifiers then true asynchronous database call will be possible with frameworks like Netty or Vert.x


texts: Database with lots of writes to a big table uses less CPU than the same database with less writes?

texts:
I'm trying to understand why InnoDB/MySQL behaves the way it does and if there's anything I can change in the configuration to fix it.

I have a table with nearly 5M rows, about 9GB in size. I also have a queue with chunks of data to be inserted into that table. On average there is about one insert every 2-3 seconds, but the database seems to sit at around 20% CPU usage. What's interesting, if I stop the worker processing the queue and wait until there's, say, 10-20k jobs to process and I restart the worker so that they get processed one after another, the CPU usage seems to be a lot lower! Only after the worker caught up with the queue and it's back to around 1 write every 2-3 seconds, the CPU usage goes up.

I've also noticed it when running the insert query by hand, with null values, if I insert them with a few seconds intervals, there seems to be a CPU spike about 1 second after every insert (which makes sense), but if run a lot of inserts quickly one after another, there's just one CPU spike after I stop the inserts.

It's a bit of a problem for me because the database runs on AWS, on t2.small instance and 20% CPU is the baseline performance level, which means I'm running out of CPU credits.

More technical details:

Ubuntu 16.04
Percona Server 5.7.19-17-log
AWS t2.small (1 CPU, 2GB RAM)

MySQL config (based on Percona's blog post):

innodb_buffer_pool_instances = 4
innodb_buffer_pool_chunk_size = 128M
innodb_buffer_pool_size = 1500M
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT

I've tried deleting all indexes, no changes.


texts: oracle decreasing vs increasing column varchar2 performance

texts:
I know it seems stupid question but why increasing the column of a table tend be fast but decreasing it takes time depend on the size of the table?

Is it because while decreasing oracle check each column size if it can be decreased? However increasing it, no need to do some checking?

Example:

Drop table ex_employee
/
create table ex_employee (id number(14) null, name varchar2(10) null)
/
insert into ex_employee
select level, level||'A' from dual
connect by LEVEL <= 20000000
/

Running the below takes 0.2 seconds

alter table ex_employee modify name VARCHAR2(100)

however decreasing it takes arround 15 seconds.

alter table ex_employee modify name VARCHAR2(10)


texts: How do I restore a SQL Server 2000 database to a SQL Server 2008 instance?


texts: Does MS Access have anything equivalent to triggers?

texts:
I'm developing some code that will eventually talk to an Oracle 10g database. While doing this, I am also redesigning the relevant database tables. I'm not a database pro, and I don't have sufficient privileges on our Oracle server (even in the development database) to create and alter tables at will. Rather than constantly harass our DBA every time I discover my design is broken, I've just created an MS Access database that I can alter as I please. I'll use the access database while I develop my code, and then we will install the updated code and create the new tables at the same time.

One of the fields in my tables is an ID number that needs to be generated when a row is inserted to the table. In Oracle I'd make a trigger for this, but I have not been able to find this functionality in Access. Is it possible to achieve the same effect in any way?


texts:
Looking for some index in one schema I get to create, I found one index with prefix sys_ such as below:

SQL> select index_name from dba_indexes where owner = 'DANILO';

INDEX_NAME
------------------------------
INDEX_SALES_ID
SYS_C0010892

Indeed, I didn't create this index, Why do I have it in my schema?


texts: Auditing of modification of change to encryption keys

texts:
Is it possible to audit/log any attempts or actual changes to the keys used in the encryption hierarchy?

We are using 2014 Enterprise edition and have recently enabled TDE across our databases and would like to add changes the the encryption keys to our auditing.

As we have recently upgraded to enterprise edition to allow us to enable TDE, I wondered if SQL Server Audit would have this out of the box?


texts: Difference in results between case syntax

texts:
I have been playing around with CASE syntax and noticed the following difference in behaviour which I don't full understand.

Given the below query

DECLARE @Value1 INT

SELECT
    'Col1' = CASE WHEN @Value1 IS NULL THEN 'Yay'
    ELSE 'Oh'
    END
    ,'Col2' = CASE @Value1 WHEN NULL THEN 'Yay'
    ELSE 'Oh'
    END
    ,'Val' = @Value1

The result set I am getting is this:

  Col1 | Col2 | Val

  Yay | Oh | NULL

I would have thought they would return the same result. Would anyone be happy to explain what's going here?


texts:
Importance of RAM is an established fact but far less material is available about the importance of cores and multithreading when it comes to the usage of CPU by MySQL. I am talking about the difference of running MySQL on 4cores vs 6cores vs 8cores and so on.

Do different storage engines use CPU differently?


texts: MongoDB 3.6 crashed due to invariant() failure

texts:
MongoDB crashed twice in 6 hours today due to invariant() failure. We have been using MongoDB 3.6.3 for more than a year now, but this is the first time that we saw this issue. The extract from the log files is given below for reference -

Log from 1st failure:

  2019-02-27T08:44:51.411+0530 E STORAGE  [conn3385299] WiredTiger error
  (12) [1551237291:411637][2344:2000827264],
  file:collection-17055--5635302636308412295.wt, WT_CURSOR.search:
  C:/MREYE/MongoDB/data\collection-17055--5635302636308412295.wt:
  handle-read: ReadFile: failed to read 4096 bytes at offset 720363520:
  Not enough storage is available to process this command.

  : Not enough space 2019-02-27T08:44:51.412+0530 F -
  [conn3385299] Invariant failure: seekRet resulted in status
  UnknownError: 12: Not enough space at
  src\mongo\db\storage\wiredtiger\wiredtiger_record_store.cpp 1734

    2019-02-27T08:44:51.412+0530 F -        [conn3385299]

  ***aborting after invariant() failure

Log from 2nd failure:

  2019-02-27T13:44:11.594+0530 E STORAGE  [conn20] WiredTiger error (12)
  [1551255251:594637][54524:2000827264],
  file:collection-17071--5635302636308412295.wt, WT_CURSOR.search:
  memory allocation of 28696 bytes failed: Not enough space

  2019-02-27T13:44:11.594+0530 F -        [conn20] Invariant failure:
  seekRet resulted in status UnknownError: 12: Not enough space at
  src\mongo\db\storage\wiredtiger\wiredtiger_record_store.cpp 1734

  2019-02-27T13:44:11.595+0530 F -        [conn20]

  ***aborting after invariant() failure

Note:

The MongoDB is on C drive and there is sufficient space left (276 GB) on that drive. The MongoDB data folder size itself is only 65 GB. Any idea what might have caused this issue?


texts: Oracle 12c query performance varies based on schema/user

texts:
I have a strange (to me) performance issue. Based on the schema that runs the query I received very different performance. Here's my setup.

Oracle 12c DB
    SAMPLE schema/user
       WIDGET table (300,000 rows/10 columns of varchars, nvarchars, dates, and numbers)
    USER1 schema/user
    USER2 schema/user
    DBA1 schema/user
--SELECT granted on WIDGET table to USER1 and USER2.
--DBA1 already has access to all table accross all schemas and has many more privs.

When I execute either of the following:

select * from SAMPLE.WIDGET;
select count(1) from SAMPLE.WIDGET;

It takes about 0.5 seconds in SAMPLE schema or DBA1 schema. It takes about 10 seconds in USER1 and USER2 schema.

So I'm looking for the following:

Under what condition(s) can query performance in Oracle differ based on the user you are logged in to when you execute a query?


texts:
We're dealing with the following situation (PG version is 7.4.30): somehow, a DB has gone missing from pg_database (psql \l listing doesn't show it anymore). More, while you can still \c to it, a \d table listing only shows a fraction of the tables. A \d on the existing tables shows missing columns as well.. However, all the tables (still visible or not) may be queried by selects via psql and the actual data and columns in the result sets seem just fine.

We backed up the data folder and for the last 24h tried to recover the situation to a point where pg_dump works. Unfortunately we haven't been fully successful yet although we tried tons of suggestions in previous similar posts. Reindex didn't help, but a "VACUUM FULL FREEZE [ANALYZE]" did free 70% (14GB to 4) of the used disk space. Of course, the database wasn't vacuumed regularly as it should have been so:

WARNING:  some databases have not been vacuumed in over 2 billion transactions
DETAIL:  You may have already suffered transaction-wraparound data loss.

A full vacuum without the freeze option does make all databases, tables and users visible, but duplicated, i.e. \l then shows the database twice and a \d on the database lists all tables twice. The postgres user appears twice in pg_user, etc. pg_dump won't work if the database is not listed and won't either if the postgres user is duplicated..

Does this sound familiar to anyone? It's a pity that all the data seems to be there, but we're not able to recover and restore it to a clean DB. We'd appreciate any suggestions.

Thank you!

PS: I've waiting for feedback from the pgsql-admin mailing list as well.


texts: SET_AUTHENTICATION_FROM_WALLET call returns ACL error

texts:
Running Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production.  Have a wallet setup containing a basic authentication credential.
PL/sql package is executing a request over https to an external server.  The acl seems to be setup correctly, as when I issue a request as below, and do some gets, I get results back.

D_Request_context := UTL_HTTP.CREATE_REQUEST_CONTEXT(
                                 wallet_path     => D_Path,
                                 wallet_password => NULL,
                                 enable_cookies  => FALSE
                              );

    D_Req := UTL_HTTP.BEGIN_REQUEST(
                  url             => D_Url,
                  method          => C_METHOD,
                  http_version    => C_VERSION,
                  request_context => D_Request_context      )

If I call the same code, but include this call

   UTL_HTTP.SET_AUTHENTICATION_FROM_WALLET(
            r         => D_Req,
            alias     => 'myalias',
            scheme    => C_SCHEME,
            for_proxy => false
         );

I get this error:

ORA-29273: HTTP request failed
ORA-06512: at line 52
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_HTTP", line 450
ORA-06512: at "SYS.UTL_HTTP"

So why does the call to BEGIN_REQUEST get through the ACL, but the SET_AUTHENTICATION_FROM_WALLET, gets an error?


texts:
I have the passcode to decrypt the function. I have read several articles about decrypting. I have logged in as DAC and tried to view it but unable. I used someone's SP from an article (SqlDecryptor) but it gives me an error 'Cannot perform alter on 'dbo.FunctionName because it is an incompatible object type'. I really need to look at the function. Does anyone have a way for me to decrypt and see the query text? Is there a way to use the passcode to decrypt? I am running SQL12.

I have never decrypted objects so forgive any ignorance please.


texts: Copy a SQL Server 2000 database to another box without interrupting the backup chain

texts:
I have a small-but-important MS SQL Server 2000 database that I'd like to copy to a development box. I understand that the usual way to do this is to make a copy of a full backup file, and restore that backup onto the dev box.

The twist here is that I've recently inherited this database and its backups are managed by a third-party tool (Groundworks). As far as I can tell, I don't have access to the backup files themselves, but only to the Groundworks interface which doesn't appear to expose them. The database has full, differential, and transaction log backups, so I can't just make a backup on my own from Management Studio, since I understand I'll invalidate the backup chain until the next full scheduled backup.

If this were SQL Server 2005, I'd get around this with a copy-only backup, but that feature doesn't seem to be there in SQL Server 2000.

Is there an approach I'm missing? The Groundworks manual doesn't seem to cover this scenario and I haven't had any luck online, but I'm rather new to DBA work and have the feeling I've overlooked something.


texts: CONCAT with multiple INSERT INTO ... SELECT ... WHERE

texts:
I have a number (currently 6) of tables within multiple schemas, and I need to insert a number of (2000) rows into a table in each schema.

The idea is that I have the procedure below, and simply change the variable at the start (schema_name and user_id), and run for each table, rather than having to run multiple inserts, and change the schema name each time.

Eg, I'd change @schema_name = 'foo1' to @schema_name = 'foo2', and @user_id = '1', to user_id = '2', then re-run the script.

SET @schema_name = 'foo1';
SET @user_id = '1';

SET @proc = CONCAT('
INSERT INTO ' ,@schema_name, '.tbl_name (tbl_id, shortlist_id, year, created_at, updated_at) SELECT "2", "1", lookup_year, now(), now() FROM "lookup_tbl" WHERE lookup_id LIKE "' ,@user_id, '";
INSERT INTO ' ,@schema_name, '.tbl_name (tbl_id, shortlist_id, year, created_at, updated_at) SELECT "2", "2", lookup_year, now(), now() FROM "lookup_tbl" WHERE lookup_id LIKE "' ,@user_id, '";
INSERT INTO ' ,@schema_name, '.tbl_name (tbl_id, shortlist_id, year, created_at, updated_at) SELECT "2", "7", lookup_year, now(), now() FROM "lookup_tbl" WHERE lookup_id LIKE "' ,@user_id, '";
INSERT INTO ' ,@schema_name, '.tbl_name (tbl_id, shortlist_id, year, created_at, updated_at) SELECT "2", "9", lookup_year, now(), now() FROM "lookup_tbl" WHERE lookup_id LIKE "' ,@user_id, '";
INSERT INTO ' ,@schema_name, '.tbl_name (tbl_id, shortlist_id, year, created_at, updated_at) SELECT "2", "8", lookup_year, now(), now() FROM "lookup_tbl" WHERE lookup_id LIKE "' ,@user_id, '";
');

PREPARE stmt FROM @proc;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

The problem that I'm having is that I can't appear to do multiple statements within a concat.

I had this trouble before, and used INSERT INTO (...) VALUES (A, B), (C, D)...,
but I don't think this is a option with the INSERT INTO (...) SELECT ... WHERE ...

Is there an alternative that I'm unaware of?

I've used 5 rows in the example, but I've actually got 2000 rows to do it with.


texts: MERGE a UNION query results down to one row per KEY value

texts:
This is a lengthy query, it polls one LIVE database for certain sales information, then a UNION runs the same exact query on the ARCHIVE database for the same information.  The results show each store's sales from the two queries on two lines each, one for each DB polled.

The ideal result would merge the results down to ONE line per store, something I'm currently doing in Excel instead.

The QUERY:

DECLARE @FROM DATETIME, @TO DATETIME

    SET @FROM = '12-30-2013 03:00:00'
    SET @TO   = '12-28-2014 03:00:00'

SELECT d.DivisionName, (SUM(PaymentAmount) - SUM(TipAmount))[CC w/o Tips], SUM(TipAmount) [Tips], SUM(PaymentAmount) [Receipts]
  FROM LFLive.POS.OrderPayments  op WITH(NOLOCK)
  JOIN LFLive.POS.Orders          o WITH(NOLOCK) ON o.OrderId=op.OrderId
  JOIN LFLive.POS.Payments        p WITH(NOLOCK) ON p.PaymentId=op.PaymentId
  JOIN LFLive.Directory.Divisions d WITH(NOLOCK) ON d.DivisionId=o.DivisionId
 WHERE op.IsVoided=0 AND op.IsDeleted=0 AND o.IsVoided=0 AND o.IsClosed=1
   AND o.OrderTypeId = 2 AND p.PaymentTypeId = 6
   AND o.DateClosed BETWEEN @FROM AND @TO
 GROUP BY d.DivisionName

 UNION

SELECT d.DivisionName, (SUM(PaymentAmount) - SUM(TipAmount))[CC w/o Tips], SUM(TipAmount) [Tips], SUM(PaymentAmount) [Receipts]
  FROM LFArchive.POS.OrderPayments op WITH(NOLOCK)
  JOIN LFArchive.POS.Orders         o WITH(NOLOCK) ON o.OrderId=op.OrderId
  JOIN LFLive.POS.Payments                 p WITH(NOLOCK) ON P.PaymentId=op.PaymentId
  JOIN LFLive.Directory.Divisions          d WITH(NOLOCK) ON d.DivisionId=o.DivisionId
 WHERE op.IsVoided=0 AND op.IsDeleted=0 AND o.IsVoided=0 AND o.IsClosed=1
   AND o.OrderTypeId = 2 AND p.PaymentTypeId = 6
   AND o.DateClosed BETWEEN @FROM AND @TO
 GROUP BY d.DivisionName
 ORDER BY d.DivisionName

Notice the LFArchive in two lines of the second half? That's the only difference.

The RESULTS:

DivisionName    CC w/o Tips     Tips            Receipts
------------    -----------     ----            ---------
Store1          96794.67        18875.97        115670.64
Store1         342488.77        67588.71        410077.48
Store2          90863.25        20353.98        111217.23
Store2         315051.32        69489.22        384540.54
Store3          82561.83        15213.77        97775.60
Store3         302811.03        54493.71        357304.74

What I'd rather have is ONE line per store...  never tried to merge down a UNION results output so not sure exactly how to ask the question.

DESIRED RESULT:

DivisionName    CC w/o Tips     Tips            Receipts
------------    -----------     ----            ---------
Store1          439283.44       86464.68        525748.12
Store2          405914.57       89843.20        495757.77
Store3          385372.86       69707.48        455080.34


texts: Is there any better way to list all the databases in Postgres except database named postgres?

texts:
SELECT datname FROM pg_database WHERE datistemplate = false AND datallowconn = true And datname NOT IN ('postgres')

Using the code above, I get all the databases except postgres as shown in the image below:

Is there any better/cleaner/nicer way than my code above to get the same result?


texts: Mysql: 1000 select queries vs 1 query ( select .. in )

texts:
I have just read about N+1 queries problem. So I have the doubt about the performance of 2 ways.
The way 1: I have 1000 queries like that:

select * from topic where user_id = 1
select * from topic where user_id = 2
...
select * from topic where user_id = 1000

I supposed the topic table has 1.000.000 rows and each query uses binary search, so I think the Big O's way is:

1000 * log2(1.000.000)

The way 2: I have 1 query:
select * from topic where user_id in (1,2,..., 1000)
I don't know the algorithm of 2nd way. So can you explain me the algorithm of (select .. in) query. Is there any case the 1st way is better than 2nd way?


texts: restore db from .bak file that contains multi backup

texts:
I backup my database and I Mistakenly saved it without a name.

what happened is that instead of replace the last file it add to it the last backup.

now I have a .bak file that has 74 backups

I want to restore the db in position 74 with query ,since restore from the wizrad always return "db in use"

I tried

alter database <dbname>
set single_user with rollback immediate

with no luck,so my only option is throw query window

any idea how to restore the one db from the file in position 74?

Thanks for any help

Baaroz


texts: SQL statement slower on as400 console than from remote odbc

texts:
I am having a strange problem on one of my i systems running 7.1

I am using jdedwards 8.0 as erp but the issue is not related as i just found out.

After some research it came down to this. I run the same simple select in a specific table. First time i run it from one fat client which connects through odbc and the second time i run it through STRSQL.

first time it takes 1 sec maximum and second time it takes 4-5 seconds.

Debug (STRDBG) shows that when running on server it has an index suggestion that already exists! and odbc machine uses it.

I tried droping and adding the index from both erp and from navigator but results are the same.

if i run the same sql from STRSQL for the same table but on our production library it runs fast like odbc case.

sorry for my ignorance but iam not even close to being a guy that knows as400...

P.S. some more info:

Its a simple SQL that i am running:

SELECT  *  FROM testDTA/F74G0904  WHERE  ( WFDCT = 'JE' AND WFDOC =
166823.000000 AND WFKCO = '00003' AND WFDGJ = 114090 AND WFJELN =
1.000000 AND WFLT = 'AA' AND WFEXTL = ' ')
The table has around 10.000.000 in both libraries.

  GREEN SCREEN - LIB1 - 4 SECS

  GREEN SCREEN - LIB2 - 1 SEC

  ODBC CLIENT - LIB1 - 1 SEC

  ODBC CLIENT LIB2 - 1 SEC

It seems to be library specific and connection specific at the same time.

DSPJOBLOG in the case of "GREEN SCREEN - LIB1 - 4 SECS" suggests an index that already exists. Also as i said tried to drop and create the index.

Take a look on the sam eSQL in different libraries, optimizer and execution plan:

Comparison images

Thanks


texts: Multiple charsets and Collations for a Multinational Database

texts:
After decades of business speaking with companies that adhere to the general Latin1 Collation, my company is facing the issue of storing information in a different charset and collation: Greek.
So, it's time to start thinking a sort of redesign of our dbs.

Given that my installation is a MS SQLServer 2008 R2, what are the best methods, or general accepted guide lines, to do such a thing?
Multiple tables? Multiple Dbs with different settings?

I'm not a DBA, I'm only asking to have a starting point to mumble upon.

Thank you very much for reading and to all who will care to reply.


texts: What is the best database design for this situation?

texts:
I'm working on building a business application for my company and struggling to pick the most appropriate database design for a particular situation.  Let's say I have the following entities:

Approval

Id
Status
...

ApprovalComment

Id
ApprovalId
Comment

Order

Id
...

Invoice

Id
...

There obviously might be multiple types of approvals and multiple objects that require approvals.  What would be the most appropriate of the following options for designing the tables:

OPTION 1

Have one approvals table with null foreign keys:

Approvals

Id PK
Status
OrderId FK NULL
InvoiceId FK NULL

ApprovalComments

Id PK
ApprovalId FK
Comment

In this case I would have to add a column for every object that needs an approval

OPTION 2

Have a parent Approvals table with common fields, and a child table for each object that needs an approval:

Approvals

Id PK
Status

ApprovalComments

Id PK
ApprovalId FK
Comment

OrderApprovals

ApprovalId PK FK
OrderId FK

InvoiceApprovals

ApprovalId PK FK
InvoiceId FK

OPTION 3

Have an approvals table for each object:

OrderApprovals

Id PK
OrderId FK
Status

OrderApprovalComments

Id PK
OrderApprovalId FK
Comment

InvoiceApprovals

Id PK
InvoiceId FK
Status

InvoiceApprovalComments

Id PK
InvoiceApprovalId FK
Comment

I know these are all valid solutions, but I can't decide which one would be best for adding different types of approvals in the future.  Any thoughts?


texts: When Azure recommends an index (part of natural key) with all other columns included, should I switch to a clustered index for this natural key?

texts:
The table does have an identity key (current CI), but it’s barely used to query. Because the natural key is not ever-increasing I’m afraid of insert performance, fragmentation or other problems that I don’t foresee now.

The table is not wide, with just a few columns. It has about 8 million rows and bringing our site to a halt during peak times. (+1000s concurrent users). The data is not easily cacheable, because it is quite volatile and essential that it’s up to date.

There are a lot of reads on one column of the natural key, but also quite active inserting and updating. Say 8 reads, vs 1 updates vs 1 inserts.

Id (PK)         int
UserId*         int
Key1*           varchar(25)
Key2*           varchar(25)
Key3*           int
LastChanged     datetime2(7)
Value           varchar(25)
Invalid         bit

* this combination is the natural primary key

I need to query the most of the time on:

All rows for one UserId (most queried)
All rows for a list of UserIds (a lot of rows)
All rows for a list of UserIds with Key1 = X
All rows for a list of UserIds with Key2 = X
All rows for a list of UserIds with Key1 = X and Key2 = X

I know the final answer is always “profile it”, but we are quite on a time constraint here so any guidance or experienced opinions in advance would really be appreciated.

Thanks in advance,


texts: Mysql replication is running poorly on one of the slaves with no apparent changes

texts:
We have been using a master with 2 slaves for as long as I can remember with no major issues. Both slaves are lower in specification than the master so I do expect there to be lags at times and have seen these before and they caught up eventually. Both slaves are virtual machines running on similar physical hardware (raid 10 disks). One of the slaves is off-site, so there is the additional overhead of VPN.
The on-site slave starting lagging a week or so ago and is now at 406039 seconds behind master. I have rsync'd the data to various different machines and various different flavours of linux to see if there is any noticeable difference in the performance and I see nothing change.
I have just restarted the slave on a fresh install of Redhat 8 with MariaDB and reset the slave config with the last known binlog name and position and both the IO and SQL threads are running. Since resetting the slave, the Master_Log_File has gone from mysql-bin.015971 to  mysql-bin.015983 (master server is currently on mysql-bin.016111).
The SQL slave however has barely moved and is probably about 40% of the way through;
         Master_Log_File: mysql-bin.015984
           Read_Master_Log_Pos: 40571609
                Relay_Log_File: mariadb-relay-bin.000002
                 Relay_Log_Pos: 310293172
         Relay_Master_Log_File: mysql-bin.015971
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes
               Replicate_Do_DB:
           Replicate_Ignore_DB:
            Replicate_Do_Table:
        Replicate_Ignore_Table:
       Replicate_Wild_Do_Table:
   Replicate_Wild_Ignore_Table:
                    Last_Errno: 0
                    Last_Error:
                  Skip_Counter: 0
           Exec_Master_Log_Pos: 359193597
               Relay_Log_Space: 13950334698
               Until_Condition: None
                Until_Log_File:
                 Until_Log_Pos: 0
            Master_SSL_Allowed: No
            Master_SSL_CA_File:
            Master_SSL_CA_Path:
               Master_SSL_Cert:
             Master_SSL_Cipher:
                Master_SSL_Key:
         Seconds_Behind_Master: 406669

When I show processlist, the SQL thread seems to be doing very little, I barely ever see if performing anything
|  9 | system user |           | NULL | Slave_IO  |  196 | Queueing master event to the relay log | NULL                  |    0.000 |
| 10 | system user |           | NULL | Slave_SQL |    0 | Apply log event                        | NULL                  |    0.000 |

I was thinking of syncing the on-site slave with the off-site slave and starting from that position, however that means moving about 1.5TB of data over a 100mb link which will take a considerable amount of time. The other alternative is that I resync from the master but I am concerned that this slip will happen again and I have no idea why.
As said, this was working for a number of years with no problems, no change in hardware and then suddenly this.


texts: Tuning postgres so it's optimized for bulk COPY imports

texts:
I am using the COPY command of Postgres to bulk insert data once a minute from an in-memory CSV file. I written the COPY code so that it performs the COPY's in parallel on separate db connections.
My server has 32gb of ram and 16cpus.
I'm getting a high rate of insertion but would like it to be as high as possible.
What are some easy wins to optimize this? I do also have some read queries occurring on the server whilst this bulk COPY is happening (at present it takes about 10 seconds to finish each time). All inserts are being handled by this COPY setup I have.
Background:
The DB server has a local SSD it's writing to, and the servers writing to the DB server are in the same data centre with private high speed connections. CPU and memory isn't being maxed out on the DB server either, which inclines me to think there is some config I am missing out on.


texts: Get transactions that failed or were never committed

texts: Is there anywhere I can look to find failed transactions in SQL server 2012?


texts: How to clone a user in SQL Server 2008 R2?

texts: Is there a way to clone users' security and permissions in Microsoft SQL Server, preferably using the SQL Server Management Studio GUI?


texts:
I am a SQL Server DBA.  I recently had the misfortune of losing a database due to drive failure. I was able to restore the data but for whatever reason, it stopped at a certain time and not all of the data was restored.

I restored the database again to a test server only to find and restore ALL of the data but the problem is, I now have to get the rest of the data that did not restore on to the server where it is supposed to be.  Can that be done?

Given all the primary key contraints and uniqueness of those keys involved, imagine we will have to renumber some of the rows that were affected because those keys have been reused once the database came back up and was operating.


texts:
I am trying to figure out if there is a way I can make nested CTEs work for this particular case.

Consider the following (highly contrived) scenario which is based on the actual application: there's a single column table of employee id's. Then there's an employee property table with all the details. (The main reason behind the single col table is often as a matter of course new employee id's need to be created in lots and assigned before any details of the actual staff is known.)

Now to the task at hand, we are inserting details (i.e. the name) for a new employee, but first we need to check if an employee with that name already exists. If it does, we'll simply return the id, and if not, we'll create a new employee record and then insert the details, finally returning the newly created id.

To recreate this test scenario:

CREATE TABLE public.employee (
    id text DEFAULT gen_random_uuid(),
    PRIMARY KEY (id)
);

CREATE TABLE public.employee_details (
    employee_id text,
    name text,
    PRIMARY KEY (employee_id),
    FOREIGN KEY (employee_id) REFERENCES public.employee(id)
);

The query I am trying to hammer into shape looks like shown below.

with
e as
    (select name, employee_id from employee_details where name = 'jack bauer'),

i as (insert into employee_details (name, employee_id)
    select 'jack bauer',
        (with a as (insert into employee values(default) RETURNING id) select a.id from a)
    where not exists (select 1 from e) returning name, employee_id)

select employee_id, name from e
union all
select employee_id, name from i;

If I replace the nested CTE with an already created id (executing the nested CTE separately), it works (but can result in the creation of a superfluous id). It is also possible to simply move the nested CTE to the top level (so the whole thing will look like with e as (..), i as (..), a as (..) select .. where not exists..., but this would also mean that a superfluous employee id is created where no details need to be inserted. I want to figure out a way to do it "inline" - so the new id will only be created if the not exists clause returns true.

I keep getting the error:

  WITH clause containing a data-modifying statement must be at the top level.

I guess the trouble is that the nested CTE returns a "column" whereas the whole query will work if it gets a "value" instead (which it does, when one simply copies in a text value instead of the CTE). I did come across a somewhat related discussion at this question mentioning an apparent bug which has been fixed since 9.3. I don't know if that is related to my troubles here. To quote from the linked discussion:

  The parse analysis code seems to think that WITH can only be attached to the top level or a leaf-level SELECT within a set operation tree; but the grammar follows the SQL standard which says no such thing

I'm using Postgres 10.3.


texts:
I'm trying to run a tiny (~100-200 MB) database in-memory, but ibdata1 grows out of control.
Situation: MariaDB is running on a resource constrained HW (Raspberry, 1GB RAM), storing non-critical sensory data (like room temperature). The real file system is an SD card, so frequent writes destroy it. Storing data in-memory would be a good option.
What is not a solution:

Memory storage engine can't handle TEXT columns
Aria storage engine can't handle foreign keys
MyRocks storage engine is not available for 32-bit platforms
SQLite ///:memory: can't handle the "load", throws uncountable 'cannot commit - no transaction is active' errors

What I've tried:

using tmpfs for datadir and tmpdir
tweaking InnoDB to minimal resource usage
disabling doublewrite buffering, disabling change buffering, using READ-UNCOMMITTED isolation

See configuration
But when I purge old data, the DELETE causes ibdata1 to grow nearly to the 100% size of the table I delete 10-15% of the rows from. My guess is that Rollbacks Segments and Undo Tablespace is still very active, but I can't "disable" MVCC altogether.
How to configure InnoDB/MariaDB to not fill ibdata1 with (rollback/undo?) data that is useless in an in-memory database?


texts: Reduce load off primary server by using log shipping, or mirroring, or something else?

texts:
I have previously asked the below question about LogShipping frequencies, but I think I might be approaching this problem incorrectly (LogShipping frequency)

Problem

I have a high volume site that I would like to divert some of the load off the primary server. The main area I wish to divert is the admin reporting queries which do not require the data to be real time (a lag of 5-10 mins is ok).

Experiments

LogShipping

This appeared to be ok, but I have an issue with the two ways that it handles connected clients.

Disconnect on restore. If I have a long running report from the web app, it will kill the report in the middle of processing.
Dont disconnect user. This is also not very good because if the admin db is getting a high amount of traffic it cannot get an exclusive lock on the db to do the restore and the lag of the data difference could push out to be more than 10+ mins.

Mirroring

This looked very promising, but seems more focused around data redundancy and the ability to have a failover db, not my actual aim of reducing overhead on the primary server.

Question

Is the something I am missing here, should I be trying something else (readonly database, better hardware, clustering), or am I completly missing the point of the above technologies?