SQL Server: Help in writing a query to get 3 rolling months average for a table Query Thus far:

select month
     , year
     , vol
     --, sum(vol) over (order by month,year  ROWS BETWEEN 2 PRECEDING AND current row) sumnetvol,
     , avg(vol) over ( order by  month,year
                       ROWS BETWEEN 2 PRECEDING
                                AND current row) '3monthra'
     , name
     , cnt
     , costx
from (
    SELECT month(convert(datetime, cast(monthnumber as nvarchar(10))+'01')) as [Month]
         , year(convert(datetime,cast(monthnumber as nvarchar(10))+'01')) as [Year]
         , sum(SumOfNetAmount) as vol
         , count(*) as cnt
         , costx
         , name
    FROM [ngl_OTB]
    GROUP BY costx
           , MONTH(convert(datetime,cast(monthnumber as nvarchar(10))+'01'))
           , YEAR(convert(datetime,cast(monthnumber as nvarchar(10))+'01'))
           , name
           , SumOfNetAmount
) s
where costx = '1000.002' and
 name = 'ngl-raw'
order by month,year

I need help on how to recursively set the [costx] and [name] columns to allow 3 months rolling avg of each [costx] and [name] correctly. All attempts provided wrong calculations like 3 months rolling avg was bleeding into other costx and name.
My goal is to group all the data by costx and name and order by year and month, then performing a rolling 3 months avg on each costx using the value of [vol] for that [costx]

Any help will be greatly apppreciated

select month
     , year
     , vol
   --, sum(vol) over (partition by costx, name order by month,year ROWS BETWEEN 2 PRECEDING AND current row) sumnetvol,
     , avg(vol) over (partition by costx, name order by month,year ROWS BETWEEN 2 PRECEDING AND current row) '3monthra'
     , name
     , cnt
     , costx
from (
SELECT month(convert(datetime, cast(monthnumber as nvarchar(10))+'01')) as [Month]
     , year(convert(datetime,cast(monthnumber as nvarchar(10))+'01')) as [Year]
     , sum(SumOfNetAmount) as vol
     , count(*) as cnt
     , costx
     , name
FROM [ngl_OTB]
GROUP BY costx
       , MONTH(convert(datetime,cast(monthnumber as nvarchar(10))+'01'))
       , YEAR(convert(datetime,cast(monthnumber as nvarchar(10))+'01'))
       , name
       , SumOfNetAmount)s
where costx = '1000.002' and name = 'ngl-raw' order by month,year


Why T-log is not truncated after T-log backup? I have a database myDB in full recovery model. I took a full backup and restored it on the test server. Afterwards, I checked the size of T-log. The size of t-log was 15600 MB and used space out of total space was 351 MB. I decided to truncate those 351 MB and ran T-log backup. Unfortunately, the size does not change at all. It again showed 351 MB. Then I took other subsequent t-log backups. The size still 351. Why t-log backup does not truncate log file? I am not asking about shrinking, I just want to know why t-log does not truncate t-log.

Here are some details regarding t-log:

From DBCC LOGINFO

From querying log_reuse_wait_desc:

About the size of T-log:

The 8th row in your first screenshot shows what appears to be an active VLF in the file. If there is a single active log record in a VLF then the overall VLF will be treated as active log and that VLF cannot be truncated.

If the server is SQL 2016 SP2 or later, you can use the below DMF to get information on the VLFs and check the active state and size. You will likely find that the active VLF is around 350 MB, and if even one byte is active, the entire VLF is considered active and backing up the log won't mark it for truncation.

SELECT s.[name], l.vlf_size_mb, l.vlf_active
FROM sys.databases s
CROSS APPLY sys.dm_db_log_info(s.database_id) l

Further reading:

What are virtual log files in a SQL Server transaction log?

SQL Server Transaction Log Backup, Truncate and Shrink Operations


How do I display subtotals in cube browser in SSAS 2012 (Sql Server Data Tools)? Apparently this feature was available in previous versions but has now been removed.

What are some alternatives?

That's right. The cube browser control within SSDT is completely different to the one that used to exist in BIDS2008 or lower. This had me going at first too. The best way i found to get around this was to launch into excel, which can be done from the cube browser tab.

I believe it even creates a temporary connection file for you too so you don't have to create that yourself. Then all you need to do is create a pivot table to display the data how you want.


percona-online-schema-change is taking a lot of time We have a table with 600M+ records and using AWS Aurora with master-slave replication.

Requirement:
We need to add two columns with index in customer table.

Aurora Instance Specs:
Ram: 30.5 GB
CPU: 4 vCPU

I replicated the cluster and created instance with same configuration and ran the update process to newly created master instance to see how it works. But it's taking a lot time; ( already been 4 days ) and still running. Is there anything I can do to speedup the alter process?

Command:

pt-online-schema-change --execute --ask-pass --user=admin --statistics --chunk-time=1 --alter 'ADD COLUMN email_sha1 CHAR(40) DEFAULT null, ADD COLUMN email_sha256 CHAR(64) DEFAULT null, ADD INDEX email_sha1(email_sha1) USING BTREE, ADD INDEX email_sha256(email_sha256) USING BTREE'  D=example,t=customer,h=example.public.rds.amazonaws.com

You are taking a hash of a phone number?  Why?  Indexing the phone number is arguably better than building a hash and indexing that.  For one thing, the phone number is always(?) shorter than the hash.

Kill the ALTER and rethink the task.

The likely reason for the problem is the randomness of the column being indexed -- Toward the end of the process, it will be slowing down to about 1 disk hit per row.  If you estimate 1ms per disk hit, I would estimate that it might take a week to finish.

What version are you running?  Can it add an index with ALTER=INPLACE?  That might let you (1) add the columns, then (2) in a separate ALTER do the ADD INDEX with ALTER=INPLACE, and not have to worry about it taking a week.

Still I question the need for the columns or the index.

If you need to discuss further, please provide SHOW CREATE TABLE.


Heterogeneous queries error with a View I recently got my first taste of the error "Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection." which I hadn't come across. Nearly every resource I found on this site and elsewhere dealt with this occurring in a stored procedure.

My specific scenario is a Vendor supplied app and DB, I can change the DB but not the app. I created a view on the DB to another server (via a linked server) but when I attempted to use this in the app I got the aforementioned error. I used SQL Profiler to prove the app opens its connections with ANSI NULLs and WARNINGS OFF. I used SSMS to prove the link works with the default values there. But unlike an SP I can not set ANSI WARNINGS on a view.

I set ANSI WARNINGS on the DB as a test, the app now worked, but I couldn't afford a full regression test of the app so this solution does not work. I also tried many different ways of implementing the linked server including created an ODBC connection on the server and then using that. But it's like the app is explicitly setting ANSI WARNINGS to off.

Not sure if matters but the source server is SQL2000 and the remote server was SQL2008R2.

As I said in the question a lot of resources dealt with this issue but not when it pertained to a view. The solution I found was to

1- create a 2nd DB on my source server.
2- create a view on the 2nd db using the linked server to the remote DB
3- alter the view on the 1st DB to use the view on the 2nd DB
4- alter the 2nd DB to use ANSI WARNINGS ON at a DB level


Does DB2 have equivalents for Oracle's UTL_ENCODE and UTL_RAW modules? I'm looking for something similar to:

utl_smtp.command(conn, utl_encode.base64_encode(utl_raw.cast_to_raw(<username>)));

I couldn't find any reference among the DB2's system-defined modules and I've read that:

  after migrating to DB2 there is a little surprise - there is nothing like UTL_RAW and UTL_ENCODE functions

that was written on March 28, 2010.

Considering that DB2's Oracle compatibility has increased with every release from v9, do the newer versions of DB2 provide any equivalents for Oracle's UTL_ENCODE and UTL_RAW?

I believe you are correct. It does not look like DB2 has built in support for base64 encoding.

So that would leave the following as options:

Handle the encoding within your programs.
Write a Java routine (stored procedure) to handle it.
Using a different encryption scheme. DB2 comes with encryption built into itself. There is an ENCRYPT function. Check it out.

Be careful with Java implementations of base64 though. A coworker of mine has mentioned he has seen a bug in all versions of it, in which "words" that have duplicate letters can decrypt into junk. So be careful and double check your base64 encoding/decoding.


How to get Postgres database name at Linux terminal I need to connect to a single postgres database but I dont know the name of the database. How can I list names of available databases from the Linux terminal ?

You can use:

psql -l

This requires a login but you can use the same credentials as you would for connecting to the target database


Understanding time format of the EXPLAIN command - Postgres When I run the EXPLAIN ANALYZE command on a given query, I'm having a difficult time interpreting the outputted time value. For example (actual time=8163.890..8163.893). Do the internal decimals represent repeating characters?? Sorry, this may be a noobish question, but I want to make sure I'm interpreting the results correctly.

   ->  GroupAggregate  (cost=2928781.21..2929243.02 rows=1 width=27712) (actual time=8163.890..8163.893 rows=1 loops=1)

actual time=8163.890..8163.893 means

Initializing that step ("startup") took 8163.890ms
Running the whole step took 8163.893ms

So in that case nearly the complete work was done in the startup phase of that step.

Edit:
The same logic is "applied" to the cost information

cost=2928781.21..2929243.02 means:

The cost to initialize this step was estimated at: 2928781.21
The cost to perform the step was estimated at: 2929243.02

(note that "cost" does not have a unit - it's an arbitrary value)

This is also explained here:
http://www.postgresql.org/docs/current/static/using-explain.html


Enable Job instantly after AOAG Role change We have a archival job which is set up in all three replicas which are part of AOAG (SQL Server 2014).

Here the connection string is AOAG Listener (which connects to only Primary Replica).

I have disabled the job in secondary replicas.

I have seen message_id 1480 (AG Role Change).

So now I should do the below step by step process to auto-enable the job at times of unexpected failover.

Create alert for 1480.
Script out the enabling code for that particular job (T-SQL).
Write a PowerShell code for identifying the event id 1480  for role change and check for the current primary replica (T-SQL Code above) and auto enable (T-SQL script) .  I will let this process run in all the replicas to identify the role change and auto_enable the archival job in secondary sync server AOAG.

Conclusion : In brief, I need to disable job in secondary replicas and enable them automatically as per the replica role change and automatically enable the archival job as per the replica state change to primary.

Please help me to get rid of this confusion.

I have checked the below links but would like to know step by step process.

(sqlmag.com) AlwaysOn Availability Groups and SQL Server Jobs, Part 5: Setting Up Failover Alerts
(mssqltips.com) Configure SQL Server Alerts and Notifications for AlwaysOn Availability Groups

Not sure if you can build a "trigger" to capture role changes.

I would probably set up a SQL Server Agent job that runs pretty frequently. Check

SELECT primary_replica
FROM sys.dm_hadr_availability_group_states;

and compare this value to a local table to detect when the primary replica changes.


Performing deltas / diffs over VPN from Oracle to SQL Server database I have inherited a reporting structure that is quite the Rube Goldberg device. I have a collection of MS Access 2003 databases that I have begun to migrate to SQL Server. My current hangup is that we have a VPN/ODBC connection to an Oracle database that belongs to an external entity, and the server for this hosted service is over 2000 miles away. While the Access "solution" does "work", it is extremely slow and any kind of development is difficult and time-consuming.

We decided that we would create a mirror database at our local site for all reporting and analytic services, and we were able to transfer a good portion of the data to our local "mirror" database using Microsoft's Oracle migration tool. Unfortunately this tool doesn't seem to provide a functionality to perform deltas.

I have been able to query the remote database with applications such as MS Access and Flyspeed SQL Query, bu they have their limitations. For example, Access 2003 is having a lot of trouble pushing data into the local SQL database. It can do very small data sets, but it chokes on anything more than a few thousand lines. Flyspeed can see all the data sets but I am unable to perform an "INSERT INTO" query for whatever reason, and it times out very easily. It also lacks the ability to schedule a query, from what I can gather, and I don't see that it can be scripted.

Is there a methodology by which I can perform deltas with the VPN / ODBC constraint, as well as with the SQL Server / Oracle differences?

Thanks,

Nate

In terms of getting the data into the SQL Server database, you can use that ODBC connection directly via the "linked servers" feature (see http://msdn.microsoft.com/en-us/library/ms188279.aspx) - this would allow you to transfer data directly from the remove Oracle instance down to your local SQL Server one with statements as simple as INSERT <localtable> SELECT <stuff> FROM <server>.<database>.<schema>.<table> WHERE <condition>. I've used this to pull data from both local and remote databases (over VPNs or other tunnels) and found it to be reliable as long as the connectivity between the sites is reliable. It is worth making sure that either your VPN or ODBC setup are performing stream compression: this will increase latency a little but could greatly improve transfer times as your data is most likely to be quite compressible.

Pulling down changes only is going to be something that will depend heavily on the database design.

The best case scenario is if everything is audited so you can easily know which updates have happened since you last picked up changes: check current "last audited action", pull down rows that have been added/changed between that and the last recorded action last time, merge these rows into your local tables, then record the ID/timestamp of the last audited action for next time. The reason to check the audit id/stamp only once at the beginning rather than for each table is to help consistency: you don't have more uptodate data in the tables you transfer last than you do in those you check first. Any other audit/history recording format can be used of course (records holding a "last modified date" and so forth).

If the data has no obvious audit structure like this then things are going to be more convoluted - comparing data in large tables between the two sites is not going to be very efficient. You can reduce the amount of data being transferred by pulling down from each table the primary key column(s) and a hash of all the others, compare the hashes with those generated locally, then request the rows that don't watch locally. You are still going to be transferring something for every row here though (for an UUID PK and a SHA1 hash that is 36 bytes/row plus any padding if transferred in binary form, and it is unlikely to be easily compressible so the VPN can speed it up that way) and if the table contains thinner rows on average than the length of the hash you should transfer the lot as you'll end up doubling (or worse) the transfer with the initial hashes.

You might find you have to use a mix of techniques (updates from audit, checksums for wide tables with no audit, just plain complete transfer for thin tables with no audit) if some of your data is audited and some not.

Another option you might be able to consider (though it requires cooperation from the other end to implement, and may have cost implications as you'll need a local Oracle license) is to use Oracle's equivalent of SQL Server's log shipping method to keep a copy up-to-date on an Oracle instance locally (and move the data from there to SQL Server or Access if that is an explicit requirement rather than just a convenience because that is what you already have installed locally). It is unlikely it that this is possible as an extra for you as it may interfere with the database's primary backup strategy, but if a log shipping equivalent (or differential backups) is being used already for the DB's backup plan then perhaps you could "piggy back" onto the arrangement by having the logs or differential backups sent to you as well as the backup infrastructure. This way the DBMS handles only pushing changes to you (though may push more than by other methods if the same rows are updated many times in the time covered by a given log segment, as you'll get the log of each change to replay not just the final result).

What you are essentially doing with any of the above is emulating replication, so if you have any say on infrastructure or replication is already in use then using that may be an option too.


postgresql pg_dumpall restore one database My environment:

# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 7.3 (Maipo)
# rpm -q postgresql
postgresql-9.2.18-1.el7.x86_64
#

I used following PostgreSQL: Documentation: 9.6: pg_dumpall to take dump all databases

  $ pg_dumpall > db.out

from what I understand, following will restore all databases:

  $ psql -f db.out postgres

my goal is, I need to restore just a one (single) database from that entire dump, how does one accomplish this task?

Your dump is in SQL format. Just open your db.out file remove what's for other databases.


SQL Server 2017 Installation Errors Installing SQL Server 2017 Standard on Windows 2016 and hitting errors when entering the new service accounts for SQL agent, service, SSIS.

'The RPC Service is unavailable, Click retry the failed action'

At the bottom of the installer page it complains that the user accounts are invalid.  I know the AD accounts work and can log into the windows server as the service accounts.

My network admin forced me to enable WINS on this server and it now works, does anyone know why or what could be being blocked as the server is behind a firewall but the local firewalls are turned off.

Looks like we have located the issue, DNS entry for the domain is wrong which is being pushed out via group policy.

Thanks to Pinal Dave for his blog post

https://blog.sqlauthority.com/2018/07/21/sql-server-install-error-validation-for-setting-agtsvcaccount-failed-error-message-the-rpc-server-is-unavailable/

Just in case the link becomes stale:

Against the properties of the Ethernet adapter, IPV4, DNS tab, in the section for 'Append these DNS Suffixes' we have an incorrect entry for domain, this is controlled by group policy (for us) we are resolving this now.


Bad bind variable in audit trail trigger in Oracle 11g R2 I have been trying to construct audit trail triggers and procedures in oracle and I am facing a problem with bind variables. Following is the code

create or replace trigger trg_audit_trail
for insert or update or delete
on tbl_mst_books
referencing NEW AS New OLD AS Old
for each row
declare
  colName VARCHAR2(50);
  cursor audit_cur is
   select column_name from all_tab_columns where table_name = 'tbl_mst_books';
begin
  for rec_audit in audit_cur loop
   colName := rec_audit.column_name;
   if inserting then
    if (inserting(colName)) then
     prc_audit_trail('tbl_mst_books',colName,:new.colName);    /*stored procedure call
              to insert audit trail data from multiple triggers to audit trail table */
    end if;
   end if;
 end loop;
end trg_audit_trail;

The stored procedure prc_audit_trail is now inserting the values passed as parameters into audit trail tables.
The problem I am having is with the bind variable :new. I am getting an error of bad bind variable. Apparently I am not being able to do :new.colName but instead of colName if I directly write the name of the column(eg: BOOK_NAME or BOOK_AUTHOR) it's not giving any error. Since I have to fetch the column name of the affected column I am fetching the column name to a variable and trying to do :new.colName. How can I resolve the issue?? Please help me. Also can I get column names of the affected columns(during insert, update and delete) in some other way?
Why am i getting the bad bind variable error for :new.colName? How can I resolve it??

P.S. I have just shown the portion for IF INSERTING but I am following the same process for IF UPDATING in the same trigger where I am getting the same error also for :old.colName

Thank you.

If you write :new.colName than you are referencing the new value of a column named "COLNAME" of table tbl_mst_books. But table tbl_mst_books has no column named "COLNAME". Actually there is no possibility to do this this way because dynamic sql does not work with :new and :old bind values. You have to program it explicitly for every column in your table and have to change your code if your table structure changes.

see AskTom


How to grant SQL Agent proxy to a public role in MSSQL 2014 I want to grant SQL Agent proxy to a public role in msdb database in MSSQL 2014.

I have the following query and I want this to return me some valid output.

USE [msdb]
SELECT
 sp.name AS proxyname
FROM dbo.sysproxylogin spl
JOIN sys.database_principals dp
  ON dp.sid = spl.sid
JOIN sysproxies sp
 ON sp.proxy_id = spl.proxy_id
WHERE principal_id = USER_ID('public');

Before I answer your question I want to draw your attention about granting permissions to Public Msdb Role on the Proxy.

Reference: Best Practices in Configuring SQL Server Agent Proxy account.

  Proper care must be taken when granting permissions to a security
  principal on a Proxy especially when granting permissions to Public
  Msdb Role on the Proxy. Every user in the database is a member of the
  public role and as a result every user gains access to the Proxy when
  Public Msdb role is granted access to the Proxy. You can query the
  sysproxylogin system table to get the list of SQL Server logins that
  are associated with each SQL Server Agent proxy account.

You can achieve this by using the following TSQL (assuming your proxy is created properly).

USE msdb ;
GO

EXEC dbo.sp_grant_login_to_proxy
    @msdb_role = N'public',
    @proxy_name = N'Proxy Name' ;
GO

Details about using sp_grant_login_to_proxy.


Joining 3 Tables with Left Join I have 2 Tables namely Area_ Table and PingData_Table

Area Table has the list of the areas along with the Area ID and Area Name

 AreaId | AreaName |  isActive
   1    |Jumbo House| 1
   2    | DCC       | 1
   3    |Dubai Mall | 1

Ping Table has the ping made by devices installed on different area location with a different ID. I ping each device every 5 mins and update the status in the table.

  Please note that no row is inserted in the table if the ping is
  unsuccessful which means the device is inactive

pkey  | AreaId  |   UpdatedOn
411   |   1     | 2018-08-08 21:54:24.810
547   |   1     | 2018-08-08 10:39:21.987
1046  |   1     | 2018-08-08 00:09:57.843

Now I want to show the above data in the following format.

Status of devices for 8 August 2018

Areaid | Name           |   Status
1      |Jumbo House     | Active
2      |DCC             | Not Active between 00:00 hrs to 23:00 hrs
3      |Dubai Mall      | Not Active between XX:00 hrs to XX:00 hrs

for all the Areas.

What I have tried till now is:

CREATE TABLE #TimeTable (hrs DATETIME)
CREATE TABLE #pingData (pid INT , areaId INT , pingTime DATETIME)
DECLARE
  @start DATETIME = '2018-08-08 0:00',
  @end   DATETIME =  '2018-08-08 23:00';
WITH x(n) AS
(
  SELECT TOP (DATEDIFF(HOUR, @start, @end) + 1)
  rn = ROW_NUMBER() OVER (ORDER BY [object_id])
  FROM sys.all_columns ORDER BY [object_id]
)
INSERT INTO #TimeTable(hrs) SELECT t = DATEADD(HOUR, n-1, @start) FROM x ORDER BY t;
INSERT INTO #pingData ( areaId  , pingTime )
SELECT DISTINCT [AreaId],CAST(FORMAT([UpdatedOn],'yyyy-MM-dd HH')+':00:00.000' as datetime) as pingtime
FROM [dbo].[Pingtable]
where UpdatedOn > @start and UpdatedOn <= @end
select  a.areaId , p.pingTime,t.hrs , a.AreaName , CASE WHEN  p.pingTime IS NULL THEN 'INACTIVE' ELSE 'ACTIVE' END as DeviceStatus
from Area_Table a
left  join #pingData p on a.areaId = p.AreaId
left   join #TimeTable t  on p.pingTime = t.hrs
ORDER BY a.areaId,t.hrs
drop table #TimeTable ;
drop table #pingData ;

Please guide me as to how to get the desired result.

I ran out of time on this, but I'm posting this here in case someone wants to extend or use my work so far.  The best I could do was find the time(s) that the area was online.  At the moment, there are a few extraneous steps in there, but this should get you close.

/** Build up Sample Data **/

SET NOCOUNT ON

DECLARE @Area TABLE
    (
    AreaID INT NOT NULL PRIMARY KEY IDENTITY(1,1)
    , AreaName VARCHAR(100) NOT NULL
    , IsActive BIT NOT NULL
    )

INSERT INTO @Area
(AreaName, IsActive)
VALUES ('Jumbo House', 1)
    , ('DCC', 1)
    , ('Dubai Mall', 1)

DECLARE @PingTable TABLE
    (
    PingTableID INT NOT NULL PRIMARY KEY IDENTITY(1,1)
    , AreaID INT NOT NULL --REFERENCES @Area (AreaID)
    , UpdatedOn DATETIME2(7) NOT NULL
    )

DECLARE @Start DATETIME2(7)
DECLARE @End DATETIME2(7)

SET @Start = '8/8/2018 00:00:00'
SET @End = '8/8/2018 23:59:59'

WHILE @Start < @End
BEGIN

    --Jumbo House
    INSERT INTO @PingTable
    (AreaID, UpdatedOn)
    VALUES  (1, @Start)

    --DCC, only active after 23:00 hours.
    IF @Start > '8/8/2018 23:00:00'
    BEGIN
        INSERT INTO @PingTable
        (AreaID, UpdatedOn)
        VALUES (2, @Start)
    END

    --Dubai Mall, inactive between 10 AM and 12:30 PM
    IF NOT(@Start BETWEEN '8/8/2018 10:00:00' AND '8/8/2018 12:30:00')
    BEGIN
        INSERT INTO @PingTable
        (AreaID, UpdatedOn)
        VALUES (3, @Start)
    END

    SET @Start = DATEADD(MINUTE, 5, @Start)

END

/** Buil up Query **/
--DECLARE @Start DATETIME2(7)
--DECLARE @End DATETIME2(7)

SET @Start = '8/8/2018 00:00:00'
SET @End = '8/8/2018 23:59:59'

;WITH CTE_Time AS
    (
    SELECT 0 AS RN
        , DATEADD(MINUTE, 0, @Start) AS TimeToUse
    UNION ALL
    SELECT RN + 1 AS RN
        , DATEADD(MINUTE, RN + 1, @Start) AS TimeToUse
    FROM CTE_Time
    WHERE DATEADD(MINUTE, RN + 1, @Start) <= @End
    )
, CTE_LagAndLead AS
    (
    SELECT A.AreaName
        , A.AreaID
        , P.UpdatedOn
        , PriorUpdateOn = LAG(P.UpdatedOn) OVER (PARTITION BY A.AreaID ORDER BY P.UpdatedOn)
        , NextUpdatedOn = LEAD(P.UpdatedOn) OVER (PARTITION BY A.AreaID ORDER BY P.UpdatedOn)
        , DR = DENSE_RANK() OVER (PARTITION BY A.AreaID ORDER BY P.UpdatedOn)
    FROM @Area AS A
        INNER JOIN @PingTable AS P ON P.AreaID = A.AreaID
    WHERE P.UpdatedOn BETWEEN @Start AND @End
    )
, CTE_Islands AS
    (
    SELECT LL.AreaName
        , LL.AreaID
        , LL.UpdatedOn
        , GroupDR = DATEADD(MINUTE, -5 * DR, LL.UpdatedOn)
    FROM CTE_LagAndLead AS LL
    )
, CTE_Islands2 AS
    (
    SELECT AreaName
        , AreaID
        , MIN(Updatedon) AS OnlineStart
        , MAX(Updatedon) AS OnlineEnd
    FROM CTE_Islands
    GROUP BY AreaName, AreaID, GroupDR
    )
SELECT AreaName
    , AreaID
    , OnlineStart
    , OnlineEnd
FROM CTE_Islands2
ORDER BY AreaID
    , OnlineStart
OPTION (MAXRECURSION 0)


Is there a way to limit the number of foreign key references for a single table index I'm having difficulty finding an answer because I'm having difficulty expressing the question. So I apologize if their is a duplicate (or similar) question on here.

Is there a way to limit the number of references to a specific foreign key in MySQL?

Explained via Metaphor:

Imagine it as a study group with a 'leader'. You have a 'leaders' table, and you have a 'members' table.

Is there a way to prevent each 'leader' from having more than 'x number' of 'members' assigned to them via foreign key?

Or is this something I would have to do when creating the 'members' in the first place? ie. some way to check how many references already exist and change the foreign key if the ID is > 'x number'

Thank you for any help or direction you can give!

I am not sure if this is a very clean solution, but it might work.

Suppose that you have a Leader table and a Member table. Create a third table, LeaderMember. This should have three columns, LeaderID, MemberID, and MemberNumber. LeaderID and MemberID are both foreign keys to the respective tables.

CREATE TYPE for the MemberNumber column, restricting the integer values that it can take, for example from 1 to 8.

Now impose two constraints on the table. First a UNIQUE constraint on MemberID, which ensures that a member can only have one leader. Then a UNIQUE constraint on the combination of columns (LeaderID, MemberNumber). This will mean that each leader can only have as many members as there are distinct values possible for MemberNumber.

I can see a couple of drawbacks with this - first of all inserting a new Member is now a little tricky, as you have to find a value of MemberNumber which has not been used. Bear in mind you have to make a check at this point anyway - to see if the Leader in question already has enough members. Secondly the type for the MemberNumber might suggest that there is some difference between the members which have the different values - that the numbers are meaningful.

However, I think it is a reasonably clean and normalized design.


Estimate number of rows in query results Given the relation:

donate(donor, type, amount, candidate, party, donate-date)

N(donate) 21,000 - total rows
V(party) = 30 - number of different parties
V(type) = 50 - number of different donation types
V(candidate) = 200 - number of different candidates

How to estimate how many rows would the following query yield?

SELECT candidate
FROM donate
WHERE party="sky" AND type "shoe"

How can I use those details to infer the average number of results?

I can tell that the upper limit is 200 as that is the number of total candidates.
I can tell that there are on average 21000/200 = 105 donations to every candidate.
There are on average 21000/30 = 700 donations to every party.
Not really sure how to proceed.

The approach would be to separate the predicate (the WHERE clause) into its parts and work out the fractions.

The fraction of all rows returned for a given column value is called the selectivity of that column. Since each value of party will return, on average, 700 rows the selectivity of party is 700 / 21000. Similarly for type the selectivity is (number of rows per value)/(total number of rows) = 420 / 21000. If it helps you can think of selectivity as being the probability that a row taken randomly from the table will have the given value.

To combine the two parts of the predicate we multiply the selectivities. We multiply because of the AND clause. Again you can think of probability - if you roll a dice what's the chance you get two sixes in a row? It's (chance of a 6) AND (chance of a six) = 1/6 * 1/6 = 1/36. This is the "chance" per row so to get the number of rows returned we multiply by the table cardinality:

   700     420
  ----- * ----- * 2100
  21000   21000

Which works out at 14.

A similar, but different, calculation can be performed for an OR clause. Then we must account for double-counting of rows that match both conditions. The answer is 700 + 420 - 14 = 1106.

Now, there are several assumptions built into this calculation. First is that the data is evenly distributed over values i.e. that there are 420 values for "shoe" and 420 for "sock" and 420 for "shirt" etc. This is rarely so. More likely is that some values occur infrequently and others often e.g. 800 rows for "shoe" and 2 rows for "golf club". This problem is know as skew. A second assumption is that the two predicates are independent. This assumes if we change "sky" to "earth" there will still be 420 rows for "shoe". Again, this is unlikely to be wholly true. Perhaps sky people only ever donate wings and earth people only ever donate shoes? This problem is know as correlation.  If your data has skew or correlation the maths above is no longer correct.

Commercial DBMS collect statistics about the distribution of values within a column. This helps them with skew. Correlation is difficult and not well handled even in commercial products.

Here's a link which discusses selectivity and cardinality and here's one which discusses multi-column predicates. They're specifically for SQL Server but the maths applies elsewhere.

  I can tell that the upper limit is 200 as that is the number of total candidates.

This is not so. From what you've shown in the question (and I'm sure that's not your full database schema) there is nothing to prevent all rows in the table from having the same value of party, type and candidate. So for a 21,000 row table the maximum number of rows that can be returned is 21,000 or N(donate) in your nomenclature. If there are 200 separate values in the candidate column and you require that each value is returned at most once you need to specify that requirement in the query. In SQL this is achieved with the DISTINCT clause:

SELECT DISTINCT candidate
FROM donate
WHERE party="sky" AND type "shoe"


Designing a graph like DB In my case, I have 5 directions: 1:North, 2:South, 3:East, 4:West, 0:Unknown. A user can stand in one direction of another user. If he is not standing in any direction, he will be considered in the unknown direction.

A, B, north
A, C, north
A, D, north
A, E, south
A, F, south
A, G, south
B, A, south
B, C, south
B, K, east

In the above table, B, C, D are standing in north direction of A and E, F, G are standing in direction south of A and so on.

Expected operation frequency:

I expect hardly 10 updates in a month for a user
I expect max 1000 (300 in general) entries against a user in bulk.
I expect hardly 15 insertions against a user in a month
I expect hardly 10 deletions in a month against a user

Another way I can think to design the DB for the above situation

A, B, 1, 2
A, C, 1
A, D, 1
A, E, 2
A, F, 2
A, G, 2
B, C, 2
B, K, 3

or

A, 1, [B,C,D]
A, 2, [E,F,G]
B, 2, [A, C]
B, 3, [K]

But this design will make the find, and update operations a bit complex. How should I design the DB for the above situation?

Update

I'm trying to create a DB for a social website.

I'm expecting more than 1 million users in the next 2 years.
There would be around 2000 find queries against a user in a month.

If your primary concern is with the graph of user connections, then use a graph database. There are many out there. Some are graph-processing engines are built on top of existing NoSQL or relational products.

These products are specifically optimized for the friend-of-friend type of queries typical for social networks. The schema definition and queries are typically much simpler than the same rules implemented on a relational engine.


Trying to understand how an auto increment primary key is better than no primary key and some other primary key questions I am trying to understand primary keys better, how to use them effectively in table design and in queries.

First, are the primary keys themselves used in WHERE clauses? For example, if I have a table of names and the primary key is setup as 'A' for all entries with last name starting with 'A', 'B' for all last names starting with 'B', etc. would it be best practice to have something like:

WHERE pk_field = 'B' AND last_name = 'Bluthe'

Secondly, I want to understand how an auto increment primary key would be better for performance than just something like:

SELECT last_name FROM names WHERE last_name = 'Bluthe'

If the primary key for this record is 1247 would a

WHERE pk_field = 1247

be much better. Wouldn't the quest still go through each record in that column until it finds a match.

Firstly, I agree with John M's comment to your question. You should do some reading about the concepts of database.

You probably misinterpret between accessing your data and how your data organized in the database. When you need to find names whose last_name equals to 'Bluthe', just do it:

SELECT last_name FROM names WHERE last_name = 'Bluthe'

That's it. You do not need to worry about the primary key. The thing you should worry of is whether the last_name is indexed.

When we talk about index, then the primary key comes in. Primary key is the way to address a row in a table. Hence, PK should be unique. The way you define primary key simply won't work. Index will be build on top of PK.
Let's say you have names table as follows, assuming using integer as pk

names
--------------------------------------------------
pk_field  first_name        last_name
--------------------------------------------------
1         John              Doe
2         Will              Smith
3         James             Bluthe
4         Nick              Smith

Then we create index on last_name. The index created will be like this:

--------------------------------------------------
last_name     pk_field
--------------------------------------------------
Bluthe        3
Doe           1
Smith         2
Smith         4

Upon searching last_name, database will utilize this index to locate the actual record. Searching 'Bluthe' will come up with pk_field = 3, which is then used to fetch the actual record from names table.

For me, the best practice for primary key is cheap to compare (using integer based data types), clustered with auto-increment values. With auto-increment value we do not have to worry when more than one transaction inserting to the same table at the same time. Clustered primary key will arrange the physical row in the table according to its primary key value. Thus searching based on primary key will be very fast.

HTH


What index to add in case of join by two optional fields I have query similar to the one below, it joins two tables by field which can have NULL value. The result matchs the data if both tables have the same data or consider the condition as optional.

SELECT B.X, A.Y FROM tableA A
INNER JOIN tableB B ON
    ISNULL(B.level5, A.f05) = A.f05
    AND ISNULL(B.level6, A.f06) = A.f06

The best index for table B I could think of is such which

CREATE NONCLUSTERED INDEX [Index_On_Table_B] ON B
( X ASC ) INCLUDE (level5, level6)

It helps a bit and from execution plan I can see the index is used but 90 % of the cost is spent in Nested Loops (Inner join).

Is there any way how to get this query working faster?

Any time you apply a function to a column in a query, you prevent SQL Server from being able to use an index on that column. Also, it may often not be possible for SQL Server to use one of its more powerful join operations: merge and hash joins, which require equijoins - a simple columnA = columnB. For efficient queries, as many terms in the WHERE clause, and ON clauses, as possible should be sargable.

I think what you actually wanted is

SELECT B.X, A.Y
FROM tableA A INNER JOIN tableB B
ON (B.level5 = A.f05 OR B.level5 IS NULL) AND
   (B.level6 = A.f06 OR B.level6 IS NULL)

Then I would expect that a non-clustered index on level5, level6 would do the job:

CREATE NONCLUSTERED INDEX Index_On_B ON tableB (level5, level6)

All your current index is doing is storing just the data from the columns in this query, which will cause less I/O than scanning tableB itself, assuming that there are other columns in tableB.


Role of Database Administrators I am currently working as an application developer on a project which involves the creation of a new SQL Server database. I am actively working with a group of DBAs who will play some role in the creation and maintenence of this database.

I want to understand what role the DBA normally plays in this type of scenario. It seems the DBAs consider everything to be the responsibility of the application team. I designed the schema and indexes, generated the DDL, and am currently in the process of performance testing the database. Are these tasks normally performed by development?

Also, it has been suggested that development should be responsible for the creation and maintenence of the database user accounts. We would be responsible for periodically changing the passwords on the production databases. Is this the norm? It seems to me that it should be done by the DBAs.

Of the tasks that you've listed the creation and maintenance of the user accounts in production should be done by the DBAs.  The developers shouldn't have the passwords of the production accounts at all.

Designing the tables, indexes and performance testing should all be done by the developer.  The DBA should be handling the creation of the objects on the production server, backups, etc.  The DBA will find any additional indexes which are needed after the system goes into production and create them in production.


High page fault, slow query/ insert during background index creation in MongoDB I was trying to create two compound indexes in background in mongodb. As per mongo doc and other sources, background index creation should not affect read/write.
But severe performance issue was observed. Page fault jumped to 180/s from 4/s, queries that takes 300ms to complete, started taking more than 2 seconds.
Db size : 40GB
Documents : 40 million
Previous index size : 24GB
Compound index1 : {field1 : 1, field2 : -1}
Compound index2 : {field3 : 1, field4 : -1}

DB stats   :
{
    "db" : "response",
    "collections" : 4,
    "objects" : 42253780,
    "avgObjSize" : 1008.5294166817738,
    "dataSize" : 42614180096,
    "storageSize" : 43648786160,
    "numExtents" : 44,
    "indexes" : 14,
    "indexSize" : 22113921536,
    "fileSize" : 79352037376,
    "nsSizeMB" : 16,
    "extentFreeList" : {
        "num" : 53,
        "totalSize" : 5861306368
    },
    "dataFileVersion" : {
        "major" : 4,
        "minor" : 22
    },
    "ok" : 1
}

Free -m :
             total       used       free     shared    buffers     cached
Mem:         64314      63944        370          1        867      56077
-/+ buffers/cache:       7000      57314
Swap:         3999        434       3565

Index builds can impact the performance of your database. While background index builds will not block read or writes, it is not correct to say that "index creation should not affect [the performance of] read/write".

Building an index will require paging the entire collection through memory; this is cause of the page faults being seen. You will likely want to only build a single index at a time to reduce the impact of this process.


Why mysql multiple column index match from left? Why MySQL using union index from left? For example union index(a,b,c),(a) could using index,(a,b) could using index,but (b,c) could not using index!!!Any useful tool to probe MySQL index file structure? How could this happen?

it simple and very clear for understanding without technical details:

1,4,1
1,5,1
2,1,4
2,3,1
3,0,2

1st column is sorted

2nd column sorted independent for each cluster in 1st column

3rd column sorted for each 1+2 cluster

so, (2,1,x) will always after (1,4,x), but it is not true for (x,4,1) vs (x,1,4)


Oracle Active Data Guard - OPEN_MODE STATUS I would to ask some question regarding Oracle Active Data Guard.

Currently, we are looking for an efficient way to get a real-time backup of Oracle Database. We tried to apply Oracle Active Data Guard on our testing servers running Windows Server 2012 R2 and an unlicensed Oracle 11g R2.

We applied these commands on the Physical Standby Server:

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE OPEN READ ONLY;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

We checked the OPEN_MODE status of the database:

SQL> select database_role, open_mode from v$database;

We got this as a result:

DATABASE_ROLE    OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY READ ONLY WITH APPLY

We tried to restart the Oracle Service (to simulate sudden power fluctuation or unintentional server shutdown). The OPEN_MODE status becomes:

DATABASE_ROLE    OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY READ ONLY

Is it by default that the OPEN_MODE status became READ ONLY if we restart the service? Or do we really need to have a license to fully access Oracle Active Data Guard features so that the OPEN_MODE status will remain in READ ONLY WITH APPLY permanently even if we restart the Oracle server?

When working with DataGuard, I would strongly advise using the DataGuard Broker (dgmgrl).

It greatly simplifies the cluster management process and is the tool of choice for interrogating and diagnosing problems with the cluster.

dgmgrl /
DGMGRL> show configuration
. . .
DGMGRL> show database PRIMARY_DB
. . .
DGMGRL> show database STANDBY_DB
. . .


Incorrect Sum values the quantity is multiplied by the number of rows I have this select query
   SELECT
  info.[OFNumber]
  --Get sum of item quantity from the same category (all quantity before production) PS_ProjectInfo table
  ,SUM(Case WHEN info.[FK_PSCategory]=2 then ((info.Length * info.[Quantity] )/1000)Else 0 end ) as 'm'

 -- Get sum of item quantity from the same category (Only quantity produced) PS_DailyProduction table
 ,SUM(Case WHEN info.[FK_PSCategory]=2 then PS_DailyProduction.Quantity ELSE 0 END) as 'qty'

FROM [dbo].[PS_DailyProduction]
Left JOIN PS_ProjectInfo info on info.id=PS_DailyProduction.[FK_OF]

GROUP BY info.[OFNumber]

Now if each items has produced at once I have one row in my PS_DailyProduction table ,there is no problem, But  whene any item has produced in two or more times (for example half quantity on one day and the rest onther day)her the problem show, in my PS_DailyProduction table i have more than one row and I get wrong result on
--Get sum of item quantity from the same category (all quantity before production) PS_ProjectInfo table
  SUM(Case WHEN info.[FK_PSCategory]=2 then ((info.Length * info.[Quantity] )/1000)Else 0 end ) as 'm'

The quantity is multiplied by the number of rows from PS_DailyProduction
I try this but did not work (dividing on number of rows)
--Count(PS_DailyProduction.FK_OF)
SUM(Case WHEN info.[FK_PSCategory]=2 then ((info.Length * info.[Quantity] )/1000)Else 0 end ) /Count(PS_DailyProduction.FK_OF)as 'm'

I use LEFT JOIN to get all rows from PS_ProjectInfo table even when they not produced yet

SELECT
info.[OFNumber]
--Get sum of item quantity from the same category (all quantity before production) PS_ProjectInfo table
,SUM(Case WHEN info.[FK_PSCategory]=2 then ((info.Length * info.[Quantity] )/1000)Else 0 end ) as 'm'

-- Get sum of item quantity from the same category (Only quantity produced) PS_DailyProduction table
,SUM(Case WHEN info.[FK_PSCategory]=2 then p.Quantity ELSE 0 END) as 'qty'

FROM
(
   SELECT [FK_OF], Sum(Quantity)
   FROM [dbo].[PS_DailyProduction]
   GROUP BY [FK_OF]
) p
Left JOIN PS_ProjectInfo info on info.id=p.[FK_OF]

GROUP BY info.[OFNumber]

BTW, not quite understand why you use left join, since PS_ProjectInfo seems a must


MySQL: AVG of defined variable per month I need help with the following code:

SELECT DISTINCT
    userid
    , count( userid ) as login_count
FROM
    (
        SELECT DISTINCT
            userid
            , date(FROM_UNIXTIME(date_time)) AS DAY
        FROM xcart_login_history
        WHERE status="success"
            and (action ="login" or action = "autologin")
        ORDER BY userid, DAY
    ) as login_days
WHERE login_days.DAY <
    (
        SELECT DISTINCT min(date(FROM_UNIXTIME(xcart_orders.date)))
        FROM xcart_orders
        WHERE xcart_orders.userid = login_days.userid
        GROUP BY userid
    )
GROUP BY userid;

This shows me the COUNT of logins for individual users before purchasing. It is on purpose not returning more than one per day counted.

I now need the average login number / login count before purchase on a monthly basis.

For instance, the calculation should analyze the buys in January and calculate the average logins (max 1 per day) that were necessary for this purchase.

Could please somebody help me?

Rewritten to only count logins after the last previous order:

-- Calculate the previous order date for each order
CREATE TEMPORARY TABLE Orders
(
OrderID INT NOT NULL PRIMARY KEY,
UserID  INT NOT NULL,
OrderDate   DATETIME NOT NULL,
Year    YEAR NOT NULL,
Month   TINYINT NOT NULL,
PreviousOrderDate   DATETIME
)
INSERT INTO Orders (OrderID, UserID, OrderDate, Year, Month, PreviousOrderDate)
    SELECT
        O2.orderid,
        O2.userid,
        FROM_UNIXTIME(O2.date) AS OrderDate,
        YEAR(FROM_UNIXTIME(O2.date)) AS Year,
        MONTH(FROM_UNIXTIME(O2.date)) AS Month,
        MAX(O1.date_time) AS PreviousOrderDate
    FROM
        xcart_orders AS O2
        LEFT JOIN xcart_orders AS O1 ON O2.userid = O1.userid AND O1.date_time < O2.date_time
    GROUP BY
        O2.orderid,
        O2.userid,
        O2.date

-- Calculate the average for each year and month
SELECT
    Year, Month, COUNT(*) AS Orders, SUM(PreviousLogins) / COUNT(*) AS AvgPrevLogins
FROM
    (
    -- Get the number of previous logins for each order
    SELECT
        O.OrderID, O.Year, O.Month, COUNT(L.userid) AS PreviousLogins
    FROM
        Orders AS O
        LEFT JOIN xcart_login_history AS L
            ON  O.UserID = L.userid
            -- Filter logins here in the join rather than in a WHERE clause, or you exclude orders that HAVE no previous logins (in practice, such may not exist)
            AND L.date < O.OrderDate
            AND (L.date > O.PreviousOrderDate OR O.PreviousOrderDate IS NULL)  -- Only count logins from after the last previous order (if one exists)
            AND L.status = 'success'
            AND L.action IN ('login', 'autologin')
    GROUP BY
        O.OrderID, O.Year, O.Month
    ) AS X
GROUP BY
    Year, Month
ORDER BY
    Year, Month


How to measure throughput in MongoDB I want to measure the throughput of read operations in Mongo. By throughput I mean  "how many reads the system process in a given amount of time (every second)".

I run mongotop 1 and I get:

                 ns       total    read    write    2018-05-21T08:01:25Z

 sampledb.samplecol        57ms    25ms     32ms

local.replset.minvalid     28ms     0ms     28ms

     local.oplog.rs         9ms     9ms      0ms

admin.system.indexes        0ms     0ms      0ms

admin.system.namespaces     0ms     0ms      0ms

 admin.system.roles         0ms     0ms      0ms

 admin.system.users         0ms     0ms      0ms

admin.system.version        0ms     0ms      0ms

           local.me         0ms     0ms      0ms

  local.startup_log         0ms     0ms      0ms

Is this means that the throughput of read operations is 25ms?

The mongostat tool provides you with this information.

Example output of mongostat:

insert query update delete getmore command dirty used flushes vsize   res qrw arw net_in net_out conn     set repl                time
*0     2     *0     *0       0     2|0  0.0% 0.0%       0 3.18G 16.0M 0|0 1|0   556b   57.2k    2 replset  PRI May 22 09:17:02.431

From the example above, the query field has the value of 2. This means that at that moment in time, the system processes two queries per second.

For full description of the output fields of mongostat, please see the Fields section.


Why would "Drop Database" fail on local SQL Server 2005 Express instance? I am trying to run the below code. It works for me but not for at least some of my users it doesn't work. It doesn't seem to error just no actually remove the DB's.

string sqlConnectionString=@"Data Source=.\SQLEXPRESS;Initial Catalog=master;Integrated Security=True";
var theSQL="DROP DATABASE DBName1, DBName2, DBName3, DBName4";
SqlConnection conn=new SqlConnection(sqlConnectionString);
Server server=new Server(new ServerConnection(conn));

server.ConnectionContext.ExecuteNonQuery(theSQL);

Alternatively, what I really need is to remove the existing Replication Sets on those DB's so that I can setup the new ones. We moved to a new SQL Server but the database names are the same.

  The subscription could not be initialized using an initial snapshot
  because one or more tables in the subscription belong to another
  publication, and therefore cannot be dropped. Consider initializing
  the subscription manually without using a snapshot.

Error they receive, though this would be after the above was supposed to run as this is during the Sync that happens next.

Depending on whether or not these are subscriptions or the published database you are going to need to drop the replication data.

MS Published a handy doc with example scripts here http://support.microsoft.com/kb/324401

This one is a general tool to clear out what you need

sp_removedbreplication '<Database name>'


Dropping indexes doesn't seem to release space to the OS. Can it? I'm running PostgreSQL 9.1. I have noticed really alarmingly bad indexes are taking up a lot of space on my server, e.g:

evlampts=# SELECT
  pg_size_pretty(pg_relation_size('mcdata_2011_07')) AS relation_size,
  pg_size_pretty(pg_total_relation_size('mcdata_2011_07')) AS total_relation_size,
  pg_size_pretty(pg_indexes_size('mcdata_2011_07')) AS indexes_size;

 relation_size | total_relation_size | indexes_size
---------------+---------------------+--------------
 43 GB         | 100 GB              | 57 GB
(1 row)

I can then see that, while a good chunk of this is the primary key, I have 11 GB in one index and 16 in another:

evlampts=# SELECT
  pg_size_pretty(pg_relation_size('mcdata_2011_07_timestamp_idx')) AS timestamp_idx_size,
  pg_size_pretty(pg_relation_size('mcdata_2011_07_host_idx')) AS host_idx_size;

 timestamp_idx_size | host_idx_size
--------------------+---------------
 11 GB              | 16 GB
(1 row)

If I then proceed to drop these indexes, I see no dramatic improvement in disk space in the OS:

$ df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/md0              1.8T  1.8T   32G  99% /export/home/lando

$ psql evlampts
evlampts=# drop index mcdata_2011_07_host_idx;
DROP INDEX
evlampts=# \q

$ df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/md0              1.8T  1.8T   32G  99% /export/home/lando

If the space was released to the OS, I'd expect there to be another 16 GB available. Is there a VACUUM step I'm missing? I have tuned autovacuum to run less frequently and may have gone too far, but I don't see anything in the documentation saying a VACUUM is necessary to free index space. What am I not doing I should be doing?

The space should be freed as soon as the index is dropped.  Possible explanations for what you show above are:

The index is in a tablespace on a different filesystem from the one
you're checking.
The OS/filesystem has some lag in providing up-to-date free space
information.
Something else is eating the free space as soon as it becomes available.
Something is holding open index files; lsof might be useful here.

If you run this query, you can see a little information about the index, including the filesystem number and the number used for filenames making up the index.

select oid::regclass, reltuples, relpages, reltablespace, relfilenode
  from pg_class
  where oid = 'mcdata_2011_07_host_idx'::regclass;

Look for files with filenames based on relfilenode both before and after dropping the index.


Schema based replication in Oracle using Data Guard We have active-passive database replication in our system cluster, so passive db belongs to DR class of nodes. We're trying to design zero-downtime deployment so I went out with few ideas.

Since we have 2 databases schemas, is it possible to replicate only one and second one (which is not replicated) to use in full read-write mode?

Data guard is used to replicate data. In previous company, we did such thing easily with MySQL (free edition), but not sure is it possible with Oracle. For me, if MySQL can do it, Oracle can as well, but internet is not providing me the exact answer on it.

I read somewhere that it's possible to do some EXCLUDE commands and to limit what is going to be replicated, but need to use that excluded schema in full read-write mode.

Thanks

If you want to do replication from one database to another, but skip certain schemas, then a Data Guard logical standby database may be a good fit for you.

However, if you want to perform replication from within the same database at the schema level, then Oracle GoldenGate "may" be able to do that... however, I've never used GoldenGate, so I can't confirm if that would be a good fit.

This answer assumes you want to do replication from one database to another, but skip certain schemas.

A logical standby database uses logminer and SQL Apply to replay the SQL statements on the standby database.  You can have a very fine grained control over what is replicated and what isn't; however, administering a logical standby is far more complicated than administering a physical standby, therefore, if you aren't familiar with Data Guard, I would become very familiar with administering a physical standby before attempting to administer a logical standby database.  In addition, a logical standby is open in read/write, and you can control what objects can and can't be modified directly by turning off SQL Apply for those objects (or entire schemas), and setting GUARD to standby.

1) Create a Data Guard physical standby

2)  Convert a physical standby to a logical standby

--STANDBY
alter database recover managed standby database cancel;

--PRIMARY
execute dbms_logstdby.build;

--STANDBY
alter database recover to logical standby keep identity;
alter database open;
alter database start logical standby apply immediate;

3)  Skip replication (DDL and DML) for an entire schema (assuming HR is the schema you wish to skip)

--STANDBY
alter database stop logical standby apply;

EXECUTE DBMS_LOGSTDBY.SKIP(STMT => 'SCHEMA DDL', schema_name => 'HR', object_name => '%');
EXECUTE DBMS_LOGSTDBY.SKIP(STMT => 'DML', schema_name => 'HR', object_name => '%');

alter database start logical standby apply immediate;

4)  Allow changes to objects not being maintained through SQL Apply

alter database guard standby;

The 12c docs on Data Guard logical standby databases can be found here:

Creating a logical standby database

Managing a logical standby database


What is the benefit of creating an Oracle context over just granting package execution? I have been programming oracle row-level security using DBMS_RLS for several years, but I have actually skipped creating contexts.  I have seen the intermittent recommendations to CREATE CONTEXT and now I'm trying to pull the trigger.

But I'm not seeing much benefit to a context at session level. I attach a package, but I still have to grant execute on it to any users I want to be able to use it. What am I missing?

As far as I can tell, the only thing that creating the context with the package is that the package can issue DBMS_SESSION.SET_CONTEXT naming that context.  After that a call to SYS_CONTEXT with that context will get the value set.

However, SYS_CONTEXT is itself kind of a hack.  It only returns a VARCHAR2 rather than a typed value.  Why not just add a function to the same package with the right data type?

Anything I put into the context with the context package can also be available through that same package.

Now, creating a global context seems like it might have benefits sharing across sessions, except that's not my goal here.

But I'm not seeing much benefit to a context at session level. I attach a package, but I still have to grant execute on it to any users I want to be able to use it. What am I missing?

Security

IMHO - Using SYS_CONTEXT() (instead of a Package) is a Security thing.  The only way to set SYS_CONTEXT() values is through a Package.  This package will most likely have access to data that the user does not.

This extra access can be done by:

Creating the Package as a Definer's Rights package
Creating the Package as an Invoker's Rights package and Grant it a Role (12.1+).

For extra security, the Context Package is not create in any of your DATA_SCHEMAs, CODE_SCHEMAs, or Parsing Schemas (eg the connected user or APEX Application Parsing Schema).  As such, I recommend you use place it in a separate "Security Schema".


Ignore accents in 'where' In our database we have multiple entries with caron/hatschek.
Now our users want to find entries including caron/hatschek when they search for entries without. I'll show this by a simple example:

In our database we have the entry (contact with name)

Millière

so this name is correct in the country the person lives in.

In our country we do not have any characters with caron/hatschek, therefore our user searches for Milliere. No results come up, as è does obviously not match e.

I have no idea how this could be realized as é,è,ê and many more are available (and this is only an example for letter e...).

(The other way would be much easier, as I could simply string replace all letters with caron/hatschek with the basic one. Obviously, our users do want the correct version of the name in the database, not the crippled one.)

This problem can be solved using accent insensitive collations.

Your database is probably using a AS (Accent Sensitive) collation so by default it will search for the exact match including accents.

You could instruct the WHERE clause to use another collation than the database default by specifying a collation with the comparison.

In this dbfiddle I created an example using the LATIN1 collations but you could use the same approach with the collation you are using by just changing AS into AI for the collation your column is currently using.

Use the Accent Insensitive collation that matches the collation the colummn is using. For example if the column is using SQL_Latin1_General_CP1_CI_AS, use SQL_Latin1_General_CP1_CI_AI and not Latin1_General_CI_AS or Latin1_General_100_CI_AS or any of the variations of those two since the behavior of the non-SQL_ collations will differ in more ways than just accent-insensitivity, and that might not be expected by users.

You can check the current collation in sys.columns.

CREATE TABLE testaccent (name nvarchar(50));
GO
INSERT INTO testaccent (name) VALUES ('Millière') , ('Milliere');
GO
-- returns Miliere
SELECT * FROM testaccent WHERE name = 'Milliere';

-- returns both
SELECT * FROM testaccent WHERE name='Milliere' COLLATE Latin1_General_CI_AI

--only returns Miliere
SELECT * FROM testaccent WHERE name='Milliere' COLLATE Latin1_General_CI_AS

Read through Using SQL Server Collations for more information.

Then again you'd probably want sorting to use this collation (as peufeu noted in the comments) to ensure that "é" sorts with "e". Otherwise, someone who paginates through results in alphabetical order would be surprised not to find the "é" where they expect them to be, but if you only want to touch this query you can add the COLLATE clause to the ORDER BY too.

As noted by Solomon Rutzky in the comments, if this only affects 1 or a few columns, another option is to create a non-persisted computed column that simply repeats the "name" column and provides the accent insensitive collation, and then index the computed column. This avoids the scan caused by changing the collation within the query. Then the query needs to filter on the new column.

Something like:

ALTER TABLE
dbo.[table_name] ADD [SearchName] datatype_of_name_column
AS ([Name] COLLATE LATIN1_GENERAL_100_CI_AI));

CREATE INDEX [IX_table_name_SearchName]
ON dbo.[table_name] ([SearchName] ASC);

Or you could also create a view instead of adding a computed column (as jyao prefers).


Are explicit transactions needed in this while loop? SQL Server 2014:

We have a very large (100million row) table, and we need to update a couple of fields on it.

For log shipping, etc, we also, obviously, want to keep it to bite-size transactions.

If we let the below run for a bit, and then cancel/terminate the query, will the work done so far all be committed, or do we need to add explicit BEGIN TRANSACTION / END TRANSACTION statements so that we can cancel any time?

DECLARE @CHUNK_SIZE int
SET @CHUNK_SIZE = 10000

UPDATE TOP(@CHUNK_SIZE) [huge-table] set deleted = 0, deletedDate = '2000-01-01'
where deleted is null or deletedDate is null

WHILE @@ROWCOUNT > 0
BEGIN
    UPDATE TOP(@CHUNK_SIZE) [huge-table] set deleted = 0, deletedDate = '2000-01-01'
    where deleted is null or deletedDate is null
END

Individual statements -- DML, DDL, etc -- are transactions in themselves. So yes, after each iteration of the loop (technically: after each statement), whatever that UPDATE statement changed has been auto-committed.

Of course, there is always an exception, right? It is possible to enable Implicit Transactions via SET IMPLICIT_TRANSACTIONS, in which case the first UPDATE statement would start a transaction that you would have to COMMIT or ROLLBACK at the end. This is a session level setting that is OFF by default in most cases.

  do we need to add explicit BEGIN TRANSACTION / END TRANSACTION statements so that we can cancel any time?

No. And in fact, given that you want to be able to stop the process and restart, adding an explicit transaction (or enabling Implicit Transactions) would be a bad idea since stopping the process might catch it prior to it doing the COMMIT. In that case you would need to manually issue the COMMIT (if you are in SSMS), or if you are running this from a SQL Agent job, then you do not have that opportunity and might end up with an orphaned transaction.

Also, you might want to set @CHUNK_SIZE to a smaller number. Lock escalation generally happens at 5000 locks acquired on a single object. Depending on the size of the rows and if it is doing Row locks vs Page locks, you might be going over that limit. If the size of a row is such that only 1 or 2 rows fit per each page, then you might always be hitting this even if it is doing Page locks.

If the table is partitioned then you have the option of setting the LOCK_ESCALATION option (introduced in SQL Server 2008) for the table to AUTO so that it locks only the partition and not the entire table upon escalating. Or, for any table you can set that same option to DISABLE, though you would have to be very careful about that. See ALTER TABLE for details.

Here is some documentation that talks about Lock Escalation and the thresholds: Lock Escalation (it says is applies to "SQL Server 2008 R2 and higher versions"). And here is a blog post that deals with detecting and fixing lock escalation: Locking in Microsoft SQL Server (Part 12 – Lock Escalation).

Unrelated to the exact question, but related to the query in the question, there are a few improvements that could be made here (or at least it seems that way just from looking at it):

For your loop, doing WHILE (@@ROWCOUNT = @CHUNK_SIZE) is slightly better since if the number of rows updated on the last iteration is less than the amount requested to UPDATE, then there is no work left to do.
If the deleted field is a BIT datatype, then isn't that value determined by whether or not deletedDate is 2000-01-01? Why do you need both?
If these two fields are new and you added them as NULL so it could be an online / non-blocking operation and are now wanting to update them to their "default" value, then that wasn't necessary. Starting in SQL Server 2012 (Enterprise Edition only), adding NOT NULL columns that have a DEFAULT constraint are non-blocking operations as long as the value of the DEFAULT is a constant. So if you aren't using the fields yet, just drop and re-add as NOT NULL and with a DEFAULT constraint.
If no other process is updating these fields while you are doing this UPDATE, then it would be faster if you queued the records that you wanted to update and then just work off that queue. There is a performance hit in the current method as you have to re-query the table each time to get the set that needs to be changed. Instead, you could do the following which only scans the table once on those two fields and then issues only very targeted UPDATE statements. There is also no penalty from stopping the process at any time and starting it later since the initial population of the queue will simply find the records left to update.

Create a temporary table (#FullSet) that just has the key fields from the clustered index in it.
Create a second temporary table (#CurrentSet) of that same structure.
insert into #FullSet via SELECT TOP(n) KeyField1, KeyField2 FROM [huge-table] where deleted is null or deletedDate is null;

The TOP(n) is in there due to the size of the table. With 100 Million rows in the table, you don't really need to populate the queue table with that entire set of keys, especially if you plan on stopping the process every so often and restarting it later. So maybe set n to 1 million and let that run through to completion. You can always schedule this in a SQL Agent job that runs the set of 1 million (or maybe even less) and then waits for the next scheduled time to pick up again. You can then schedule to run every 20 minutes so there will be some enforced breathing room between sets of n, but it will still finish the entire process unattended. Then just have the job delete itself when there is nothing more to do :-).
in a loop, do:

Populate the current batch via something like DELETE TOP (4995) FROM #FullSet OUTPUT Deleted.KeyField INTO #CurrentSet (KeyField);
IF (@@ROWCOUNT = 0) BREAK;
Do the UPDATE using something like: UPDATE ht SET ht.deleted = 0, ht.deletedDate='2000-01-01' FROM [huge-table] ht INNER JOIN #CurrentSet cs ON cs.KeyField = ht.KeyField;
Clear out the current set: TRUNCATE TABLE #CurrentSet;

In some cases it helps to add a Filtered Index to assist the SELECT that feeds into the #FullSet temp table. Here are some considerations related to adding such an index:

The WHERE condition should match the WHERE condition of your query, hence WHERE deleted is null or deletedDate is null
At the beginning of the process, most rows will match your WHERE condition, so an index isn't that helpful. You might want to wait until somewhere around the 50% mark before adding this. Of course, how much it helps and when it is best to add the index vary due to several factors, so it is a bit of trial and error.
You might have to manually UPDATE STATS and/or REBUILD the index to keep it up to date since the base data is changing quite frequently
Be sure to keep in mind that the index, while helping the SELECT, will hurt the UPDATE since it is another object that must be updated during that operation, hence more I/O. This plays into both using a Filtered Index (which shrinks as you update rows since fewer rows match the filter), and waiting a little while to add the index (if it's not going to be super helpful in the beginning, then no reason to incur the additional I/O).

UPDATE: Please see my answer to a question that is related to this question for the full implementation of what is suggested above, including a mechanism to track status and cancel cleanly: sql server: updating fields on huge table in small chunks: how to get progress/status?


How to Implement Different Object Types as Similar Tables? I have a database I am attempting to design. I am trying to determine the best approach to declare similar, but different types of objects, each with their own sets of properties (perhaps some properties identical between objects).

Let's say, for example, I am building a mall web app. I want to set up a facility for shop owners to register their stores, type of store and some specific information for that store. The problem I am faced with is the best approach to model different shop types and how to incorporate a model for data storage based on the type of shop.

To further extend the example with some concrete data (let's just pretend this information is all necessary for storage), what would be the best approach to model something like this:

Thai Fooooood
Shop Type: FoodShop
Most Popular Item: Pad Thai
Visitors Last Week: 368

Anti-Static Wristband Repair
Shop Type: RepairShop
Current Computer Repairs: 26
Repair Length Estimate (in business hours): 4
Visitors Last Week: 182

Macroputers
Shop Type: RetailShop
Next Promotion Date: January 2, 2015
Current Week Sales Estimates: 122418
Visitors Last Week: 411

Assuming many other instances of these shop types exist, with each type storing the same data. Does it make more sense to set up a table for each shop type, to use a single table to represent all shop types (using generic data), or is there a better approach?

Assuming that the app will be treating these various shop-types in the same manner for some operations (operations that do not care what type of shop that it is), then my preferred approach is to use a subclass / inheritance model. I have detailed this out in a couple of other answers here:

Don't know how to transform variable entity into relational table
Can't convert this type of ER diagram into SQL or relational tables

The basic concept would be as follows (using Microsoft SQL Server T-SQL semantics).

First you need the main entity table. It represents various "types" so those need to be defined in a lookup table (and the lookup table should have a matching enum in the app layer). Please note that I am using a TINYINT here which holds values from 0 to 255. Use the smaller numeric type that will meet your needs.

CREATE TABLE dbo.ShopType
(
  ShopTypeID TINYINT NOT NULL PRIMARY KEY, -- NO IDENTITY / auto-increment!
  ShopType   VARCHAR(50) NOT NULL
);

CREATE TABLE dbo.Shop
(
  ShopID     INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
  ShopTypeID TINYINT NOT NULL REFERENCES dbo.ShopType (ShopTypeID), -- FK to ShopType
  ShopName   NVARCHAR(100) NOT NULL,
  VisitorsLastWeek INT NOT NULL DEFAULT (0),
  -- {OtherCommonFields},...
  CreateDate DATETIME NOT NULL DEFAULT (GETDATE()), -- or GETUTCDATE()
  ModifiedDate DATETIME NOT NULL DEFAULT (GETDATE()) -- or GETUTCDATE()
);

Next you create a subclass table for each particular "type". It is sometimes appropriate to share a subclass table for multiple "types", but just be careful when doing that so one type doesn't evolve differently than the other type(s) that might be sharing the same subclass table. Please note that the PK of the subclass tables is the same field as the PK in the parent class (i.e. the [Shop] table) and even FKs to it. The reason is that the row in both tables (e.g. [Shop] and [ShopRepair] that have the same value for [ShopID] are together a complete entity; the row in the subclass table does not represent anything by itself and doesn't need a separate ID.

CREATE TABLE dbo.ShopFood
(
  ShopID INT NOT NULL PRIMARY KEY REFERENCES dbo.Shop(ShopID),
  MostPopularItem  NVARCHAR(50) NULL,
  ModifiedDate DATETIME NOT NULL DEFAULT (GETDATE()) -- or GETUTCDATE()
);

CREATE TABLE dbo.ShopRepair
(
  ShopID INT NOT NULL PRIMARY KEY REFERENCES dbo.Shop(ShopID),
  CurrentComputerRepairs SMALLINT NOT NULL DEFAULT (0),
  RepairEstimatedHours TINYINT NOT NULL DEFAULT (0),
  ModifiedDate DATETIME NOT NULL DEFAULT (GETDATE()) -- or GETUTCDATE()
);

CREATE TABLE dbo.ShopRetail
(
  ShopID INT NOT NULL PRIMARY KEY REFERENCES dbo.Shop(ShopID),
  NextPromotionDate DATETIME NULL,
  CurrentWeekSalesEstimate MONEY NULL,
  ModifiedDate DATETIME NOT NULL DEFAULT (GETDATE()) -- or GETUTCDATE()
);

To simplify SELECT operations, you can create Views against each type, if you like:

CREATE VIEW dbo.FoodShop AS
SELECT sh.ShopID, sh.ShopTypeID, sf.MostPopularItem
FROM   dbo.Shop sh
INNER JOIN dbo.ShopFood sf
        ON sf.ShopID = sh.ShopID;

CREATE VIEW dbo.RepairShop AS
SELECT sh.ShopID, sh.ShopTypeID, sr.CurrentComputerRepairs, sr.RepairEstimatedHours
FROM   dbo.Shop sh
INNER JOIN dbo.ShopRepair sr
        ON sr.ShopID = sh.ShopID;

CREATE VIEW dbo.RetailShop AS
SELECT sh.ShopID, sh.ShopTypeID, sr.NextPromotionDate, sr.CurrentWeekSalesEstimate
FROM   dbo.Shop sh
INNER JOIN dbo.ShopRetail sr
        ON sr.ShopID = sh.ShopID;

EDIT:
I forgot to add in the "audit" fields of [CreateDate] and [ModifiedDate]. I have now added them to the tables above. Please note that:

these fields are not in the [ShopType] table as there really is no purpose here, but that is a bit subjective and some people still like to know at least when the records were created, so maybe [CreateDate] is fine there
the [CreateDate] field is not in the subclass tables as the assumption is that those rows are created at the same time as the parent record. But when it comes to updates, those might just touch one of the two tables, depending on which fields are being updated.  If using SQL Server, it is possible to create the records for both tables in a single statement even, using the OUTPUT clause as follows:

CREATE PROCEDURE dbo.Shop_CreateRepairShop
(
  @ShopName NVARCHAR(100),
  @CurrentComputerRepairs SMALLINT,
  @RepairEstimatedHours TINYINT,
  @ShopID INT OUTPUT = -1
)
AS
SET NOCOUNT ON;

INSERT INTO dbo.Shop (ShopName, ShopTypeID)
  OUTPUT INSERTED.ShopID, @CurrentComputerRepairs, @RepairEstimatedHours
  INTO   dbo.ShopRepair (ShopID, CurrentComputerRepairs, RepairEstimatedHours)
  VALUES (@ShopName, 2); -- assuming ShopTypeID for "Repair Shop" = 2

SET @ShopID = SCOPE_IDENTITY();


How to export data from sql server 2008 to excel and email as attachement I'm new. Can anyone advise how I can export data from database sql 2008 to excel and email as attachment?

You can use SSIS (SQL Server Integration Services). Simple example:

Control Flow task 1 - Data Flow:

OLEDB Source. Choose the source table/view/use a query...
Excel destination.

Control Flow task 2 - Send Mail Task. Attach the above generated Excel.

You can build the "Export from SQL to Excel" part also via the SQL Server Import and Export Wizard in BIDS. Check this video for a demo.
For the Send Mail Task, check this BOL article.

If you want to automate and schedule it, you can use a SQL Agent scheduled job with an SSIS job step. A video demo is available here.
If you automate it on a server, make sure Microsoft Access Database Engine 2010 Redistributable is installed (you do not need Excel there, just the "drivers").


Can't get past password lock on fresh install of postgreSQL I feel really dumb writing this, I wanted to have a mess around with databases so I just installed postgreSQL (9.1)

During the installation I am asked to provide a superuser password. I provide a password that it can use and everything gets installed.

I'm following the getting started documentation. getting started documentation and attempt to use the command createdb in cmd.exe. It asks for a password and responds with:

createuser: could not connect to database postgres: FATAL:  password authentication failed for user "VanGOss"

I can access a database inside pgAdminIII with the password I originally provided at installation. So it's the command line tools not accepting the password.

I also run postgres and got this back:

Execution of PostgreSQL by a user with administrative permissions is not
permitted.
The server must be started under an unprivileged user ID to prevent
possible system security compromises.  See the documentation for
more information on how to properly start the server.

I tried using createuser to make a user, again run from cmd.exe and it said its not accepting my password.

what i'm thinking is I must run my environment under a limited user, which I don't want to do. This is my home computer and I just want one account.

windows 7 64-bit
1 user account, me, admin
can't past page 2 of a tutorial on running a database, kinda shameful :(

Any one know how I can make my own database? Please.

Try to add the option -U postgres to your createdb command.  The error clearly states that PostgreSQL tried to execute it with the current Windows username, while you set superuser password (presumably) for user postgres.


Is WITH RECURSIVE the right tool for my task? Table setup:

create table users(id bigserial, group_id bigint);
insert into users(group_id) values (1), (1), (2), (1), (3), (3);

Query:

    WITH RECURSIVE r AS (
      SELECT users.id, users.group_id, 0 as i
      FROM users

      UNION

      SELECT users.id, users.group_id
          , (CASE WHEN users.group_id = lag(users.group_id) over (order by users.id)
                  THEN (r.i)
                  ELSE (r.i+1) END) AS i
       FROM users
       INNER JOIN r ON users.id = r.id
       WHERE r.i < 5)
    SELECT * FROM r;

I'm trying to iterate through user, and assign i if previous group_id value is equal to current, i+1 otherwise, starting from 0.

However, this recursive query doesn't work as I expect, see: http://sqlfiddle.com/#!15/ad3b6/39

Simpler and faster (and correct) with window functions and a subquery:

SELECT id, group_id, count(step) OVER (ORDER BY id) AS i
FROM  (
   SELECT id, group_id
        , lag(group_id, 1, group_id) OVER (ORDER BY id) <> group_id OR NULL AS step
   FROM   users
   ) sub;

id|group_id| i
--+--------+--
 1|       1| 0
 2|       1| 0
 3|       2| 1
 4|       1| 2
 5|       3| 3
 6|       3| 3

It's the same basic logic as discussed and explained in depth here (just simpler, without aggregation of rows):

Grouping or Window

There are also working solutions with a recursive CTE like you tried under the linked question, but those are substantially slower and more complex.

Andthere is also a plpgsql solution. Also more complex, but fastest.
Here is an optimized variant for your simple case without aggregation:

CREATE OR REPLACE FUNCTION f_users_i()
  RETURNS TABLE (id bigint, group_id bigint, i bigint) AS
$func$
DECLARE
   _last_group_id bigint;                 -- aux var to remember last group
BEGIN
   i := 0;                                -- init to 0

   FOR id, group_id IN
      SELECT u.id, u.group_id FROM users u ORDER BY u.id
   LOOP
      IF group_id <> _last_group_id THEN  -- next group starts
         i := i + 1;
      END IF;

      _last_group_id := group_id;         -- remember last group
      RETURN NEXT;
   END LOOP;
END
$func$  LANGUAGE plpgsql;

Call:

SELECT * FROM f_users_i();

Same result.

dbfiddle here (Postgres 9.6).
SQL Fiddle (the same, based on your fiddle on Postgres 9.3).

Aside: I doubt you need bigint for users and groups. Can probably all be just integer.


MySQL applying second incremental backup causes LSN in the ibdata files to be higher than LSN in the ib_logfiles Using the backup procedure described below, it gives warnings. These warnings start to appear from the 2nd till 6th (last) incremental backup that is being applied to the base backup.

How to solve this in the procedure?

Warning messages from innobackupex

InnoDB: The log sequence number in the ibdata files is higher than the log sequence number in the ib_logfiles! Are you sure you are using the right ib_logfiles to start up the database. Log sequence number in the ib_logfiles is 12881010402316, logsequence numbers stamped to ibdata file headers are between 12908127655307 and 12908127655307.
InnoDB: The log sequence numbers 12908127655307 and 12908127655307 in ibdata files do not match the log sequence number 12881010402316 in the ib_logfiles!
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.

And a lot of these:

2016-05-31 04:28:16 7f00d9ec5780 InnoDB: Error: page 7 log sequence number 12908127624790
InnoDB: is in the future! Current system log sequence number 12881010402316.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
InnoDB: for more information.

It ends with:

xtrabackup: ########################################################
xtrabackup: # !!WARNING!!                                          #
xtrabackup: # The transaction log file is corrupted.               #
xtrabackup: # The log was not applied to the intended LSN!         #
xtrabackup: ########################################################

Procedure to create backups

On Sunday, create a base backup

innobackupex --stream=xbstream /var/lib/mysql | nc $backup_server $port

Monday-Saturday, daily create incremental backup based on last LSN from previous backup

innobackupex --stream=xbstream --incremental --incremental-lsn=$last_lsn /var/lib/mysql | nc $backup_server $port

Procedure to import backups

Import the base backup on Sunday to the standby environment:

Copy the base backup from the backup server to /var/backups/base-<date> (leaving it untouched)
Stop MySQL service
Copy /var/backups/base-<date> stage to /var/lib/mysql
Prepare the backup in /var/lib/mysql

sudo -u mysql innobackupex --ibbackup=/usr/bin/xtrabackup --apply-log /var/lib/mysql

Start MySQL service

Import the incremental backups from Monday-Saturday to the standby environment:

Copy the incremental backup from the backup server to /var/backups/incr-<date>
Prepare the base backup to apply the incremental on it

innobackupex --apply-log --defaults-file=/root/.my.cnf --ibbackup=/usr/bin/xtrabackup --redo-only /var/backups/base-<date>

Apply incremental on base backup

innobackupex --apply-log --defaults-file=/root/.my.cnf --ibbackup=/usr/bin/xtrabackup --redo-only /var/backups/base-<date> --incremental-dir=/var/backups/incr-date

Stop MySQL service
Copy /var/backups/base-<date> stage to /var/lib/mysql
Prepare the backup in /var/lib/mysql (not in the stage dir!)

sudo -u mysql innobackupex --ibbackup=/usr/bin/xtrabackup --apply-log /var/lib/mysql

Start MySQL service

When I see messages of that nature, I usually blame the redo logs. This is where is LSN is written.

The problem may stem from not preparing the incremental backup.

What seems to be missing is the use of the --prepare option.

What this essentially does is create the backup rolled forward to a specific point in time. This is particularly true if there were transactions occurring during the backup.

It's kind of like the opposite of a mysqldump --single-transaction, which makes a logical dump based on the start time of the backup. Doing a --prepare with xtrabackup will make the redo logs roll forward to match the end time of the backup.

Note what the opening paragraph of Preparing the backup from the XtraBackup Docs says:

  After you make a backup with --backup, the next step is to prepare it. The data files are not point-in-time consistent until they’ve been prepared, because they were copied at different times as the program ran, and they might have been changed while this was happening. If you try to start InnoDB with these data files, it will detect corruption and crash itself to prevent you from running on damaged data. The --prepare step makes the files perfectly consistent at a single instant in time, so you can run InnoDB on them.

This should coalesce the LSN of the incremental after making the incremental.

For more information, please read Preparing the backup from the XtraBackup Docs


using PIVOT for a table in ORACLE 12C I got the results as below after querying the multiple tables in our database (oracle database 12c enterprise edition).

but we would like to see the table as:

there are more than 100 rows in the table and also when I write the PIVOT command in the query window, it doesn't highlighted with any colour as other commands do. so please help me out of this scenario.

-- sample data
with data as
(
  select 34 as id, 1 as an_id, 'FIRST NAME' as title,
    'ANDY' as full_title from dual union all
  select 34 as id, 1 as an_id, 'LAST NAME' as title,
    'COOL' as full_title from dual union all
  select 34 as id, 1 as an_id, 'FIRST & LAST' as title,
    'ANDY COOL' as full_title from dual union all
  select 34 as id, 2 as an_id, 'FIRST NAME' as title,
    'SCHUMACK' as full_title from dual union all
  select 34 as id, 2 as an_id, 'LAST NAME' as title,
    'LAST' as full_title from dual union all
  select 34 as id, 2 as an_id, 'FIRST & LAST' as title,
    'SCHUMACK LAST' as full_title from dual union all
  select 32 as id, 1 as an_id, 'FIRST NAME' as title,
    'MARIO' as full_title from dual union all
  select 32 as id, 1 as an_id, 'LAST NAME' as title,
    'SANTI' as full_title from dual union all
  select 32 as id, 1 as an_id, 'FIRST & LAST' as title,
    'MARIO SANTI' as full_title from dual
)
-- query
select * from data
pivot (min(full_title) for title in ('FIRST NAME', 'LAST NAME', 'FIRST & LAST'))
order by id desc, an_id
;

        ID      AN_ID 'FIRST NAME'  'LAST NAME'   'FIRST & LAST
---------- ---------- ------------- ------------- -------------
        34          1 ANDY          COOL          ANDY COOL
        34          2 SCHUMACK      LAST          SCHUMACK LAST
        32          1 MARIO         SANTI         MARIO SANTI

Note that PIVOT requires an aggregate function, I could have used for example MAX as well.


Should pivot tables have more columns (many to many relationship) I'm currently thinking about how to organize my tables, and I have some information that I'm not sure were should be, one of them is the quantity of a food in a refrigerator

Is it right if I add quantity in refrigerator_has_food table? Or should this table only have foreign keys?

There is no restrictions on data stored in the pivot table. The simplest case of two IDs establishes only qualitative relations between fridges and foods: this food is stored in this fridge. If you add the counter you just turn the qualitative relation into the quantitative one: this many units of this food is stored in this fridge.

On the opposite side you have to control the counters and remove any row having counter dropped to zero to avoid silly logic yes, we store zero units of this food in this fridge.


Database backups in Oracle - Export the database or use other tools? One of the "practices" that I've seen about being performed by DBA's in my organization is to treat a full database export using tools like exp / expdp as backup.

Would this be a good practice ? What would be the advantages of using RMAN over this approach ?

The advantage of RMAN is PITR - point in time recovery. You can take an RMAN backup of the DBFs and an RMAN backup of the archived redo logs and recover your database to any point in time up to the time of the most recent archived redo log backup. The disadvantage of this approach is that it is very coarse-grained - you can only recover at the level of the tablespace.

The advantage of exp/expdp is that you have a consistent copy of the database that you can just import into a freshly created blank database. However, you cannot roll it forward - it is at this point a completely separate, independent database with no logical relationship to its original. But it is easy to recover just a single table, or even a few rows from an export.

A good approach would be regular RMAN full and incremental datafile backups, continuous backups of archived redo logs (e.g. as soon as one archivelog backup completes, start the next one straight away so you are "streaming" the logs to tape) then educate your users in the use of exp/imp so they can perform their own "backups" (in the case of wanting an actual copy of the data) and flashback (so they can do their own recoveries in the case of DML going wrong).

Remember - a backup is intended for the DBA to recover from catastrophic failure of the hardware. It is not for the benefit of end users (or you will spend your whole time doing restores to a test system and copying a few rows back to the original!).


Transactional Replication hangs when huge transactions flow We are running push transactional replication in our production environment ( SQL server 2014 (x64) Default configuration). It hangs when thousands of live transactions flows in and this continues for hours and eventually days. So as to come out we generally stop the replication. I am not sure what causes it and also don't know how to troubleshoot this issue.
Since I am new to replication, can anybody help me out with the steps to troubleshoot the issue?

I've experienced a similar problem. It turned out to be an issue with when the "Agent history clean up: distribution" job kicks-off on the distributor. This job would hang for a long time and bring replication to a stand-still when there were a lot of transactions in the queue. Check the History for the Job to see if it's stalling at the same time you have your problems.

The solution we implemented was to push through transactions in Batches with a WAIT between batches. we had to experiment with the batch size and Wait time to get the sweet-spot that didn't cripple the distributor.


Is there anything wrong with creating a very large log file? I've been given the task of finding out why our server is slow. First of all I did not set this server up.

Is there anything wrong with setting the initial log file size to 70GB? That's what ours is currently set to, and then 10MB after that.

And just so I understand- when a 70GB log file is created, it actually takes up that much space on the drive, correct? Because I initially thought the log file may be that big because it hadn't been backed up, but I backed it up and set it to truncate the log file. It backed it up, and created a new log file, but it was still 70GB.

From a performance standpoint, is there anything wrong with this?

There is nothing wrong with setting the initial log size to 70GB, if you need that large of a transaction log.  There is a problem, however, with it set to grow in 10MB increments.  Such a small increment of growth can result in numerous VLFs (Virtual Log Files) which can result in long recovery of the database during startup and lengthen restore times.

You are correct that the log file consumes the disk space regardless of how much data is actually in the log.  Backing up the transaction log does not decrease the size of the file.  Either the file was initially created that large, or it grew to that size over time, or because of a large insert operation or something.  If it grew to that size over time, you should run DBCC LOGINFO and see how many VLFs you have.  If you have thousands, then you will want to fix that by following the steps lined out here: http://support.microsoft.com/kb/2455009